Important Filter Functions in DAX in Power BI

Filter functions in DAX are used to perform advanced filtering and retrieve specific subsets of data for calculations or visualizations. Below are some key filter functions, explained with examples using a Sample Superstore dataset.

1. FILTER()

Purpose: Returns a table that represents a filtered subset of a larger table.
Example Use Case: Retrieve all orders with a discount greater than 10%.

Dataset:

Order ID	Sales	Discount
1	100	5%
2	200	15%
3	150	20%

DAX Formula:

Filtered Orders = FILTER('Orders', 'Orders'[Discount] > 0.1)

Output (Filtered Table):

Order ID	Sales	Discount
2	200	15%
3	150	20%

2. ALL()

Purpose: Removes any filters that are applied to a table or column.
Example Use Case: Calculate the percentage of total sales for each order by ignoring existing filters.

DAX Formula:

Percentage of Total Sales = 
    SUM('Orders'[Sales]) / CALCULATE(SUM('Orders'[Sales]), ALL('Orders'))

Output:

Order ID	Sales	Percentage of Total Sales
1	100	20%
2	200	40%
3	150	30%

Another Example of ALL()

Total Sales All Categories = CALCULATE(SUM('Orders'[Sales]), ALL('Orders'[Category]))

In this example:

SUM('Orders'[Sales]) calculates the total sales.
ALL('Orders'[Category]) removes any filters that might be applied to the Category column.

This measure will give you the total sales for all categories, ignoring any category filters. You can then use this measure in your visualizations to compare it with the filtered sales.

Feel free to let me know if you need more examples or any other information on DAX functions in Power BI!

3. CALCULATE()

Purpose: Evaluates an expression in a modified filter context.
Example Use Case: Calculate total sales where the discount is greater than 10%.

DAX Formula:

Total Sales with Discount > 10% = 
    CALCULATE(SUM('Orders'[Sales]), 'Orders'[Discount] > 0.1)

Output:

Order ID	Sales	Discount
2	200	15%
3	150	20%
Result: 200 + 150 = 350

4. VALUES()

Purpose: Returns a single column table of unique values.
Example Use Case: Get the unique customer IDs from the dataset.

Dataset:

Order ID	Customer ID	Sales
1	C001	100
2	C002	200
3	C001	150

DAX Formula:

Unique Customers = VALUES('Orders'[Customer ID])

Output (Filtered Table):

Customer ID
C001
C002

5. REMOVEFILTERS()

Purpose: Clears filters from a specific column or table.
Example Use Case: Calculate total sales without filtering by region.

Dataset:

Order ID	Region	Sales
1	East	100
2	West	200
3	East	150

DAX Formula:

Total Sales Ignoring Region = 
    CALCULATE(SUM('Orders'[Sales]), REMOVEFILTERS('Orders'[Region]))

Output:

Order ID	Region	Sales	Total Sales Ignoring Region
1	East	100	450
2	West	200	450
3	East	150	450

6. KEEPFILTERS()

Purpose: Retains existing filters and applies additional ones.
Example Use Case: Apply multiple filters on a dataset.

DAX Formula:

Sales with Region = 
    CALCULATE(SUM('Orders'[Sales]), KEEPFILTERS('Orders'[Region] = "East"))

Output:

Order ID	Region	Sales	Sales with Region
1	East	100	100
2	West	200	0
3	East	150	150

7. DISTINCT()

Purpose: Returns a one-column table containing the distinct values in a column.
Example Use Case: Count the distinct regions in the dataset.

DAX Formula:

Distinct Regions = DISTINCT('Orders'[Region])

Output (Filtered Table):

Region
East
West

8. ISFILTERED()

Purpose: Checks if a column is filtered.
Example Use Case: Return sales only if a specific column is filtered.

DAX Formula:

Sales Only If Filtered = 
    IF(ISFILTERED('Orders'[Region]), SUM('Orders'[Sales]), BLANK())

Output:

If the Region column is filtered (e.g., “East”), it shows sales for filtered regions only.
Otherwise, it returns a blank value.

Summary of Key Functions

Function	Description
`FILTER()`	Returns a subset of rows from a table.
`ALL()`	Ignores all filters on a table or column.
`CALCULATE()`	Evaluates an expression in a modified filter context.
`VALUES()`	Returns unique values from a column.
`REMOVEFILTERS()`	Clears filters from a specific table or column.
`KEEPFILTERS()`	Retains existing filters while adding new ones.
`DISTINCT()`	Returns distinct values from a column.
`ISFILTERED()`	Checks if a column is filtered.

These filter functions allow for detailed data manipulation and analysis in Power BI using DAX, especially for dynamic reports and visualizations.

Important Filter Functions in DAX in Power BI