DAX Workout 026 - Delving into DAX's FILTER Function in Power BI

Workouts DAX
1

Title: Delving into DAX’s FILTER Function in Power BI

Description:

The FILTER function in DAX allows for row-level filtering of tables based on specific conditions. Understand and master its usage with this focused workout.

Scenario:

You’re analyzing a Power BI report on sales data. The dataset includes columns such as Product, Quantity Sold, Price, and Region. Employ the DAX FILTER function to extract specific insights.

Objectives:

By the end of this workout, you should be able to:

  1. Understand the syntax and usage of the FILTER function.

  2. Apply FILTER to derive specific subsets of data.

Interactive Task:

Given the following tasks, write or identify the correct DAX formula:

  1. Create a new table that contains only products sold in the “West” region.

    • Your Answer: ________________________

  2. Derive a table showcasing products that were sold at a price greater than $50.

    • Your Answer: ________________________

  3. Given the following DAX formula, identify its output:

    
CALCULATE(SUM('Sales'[Price]), FILTER('Sales', 'Sales'[Quantity Sold] > 100))

    Expected output:

    • i) Sum of prices for all products.

    • ii) Sum of prices for products sold more than 100 times.

    • iii) Average price for products sold more than 100 times.

    • iv) Number of products sold more than 100 times.

Questions:

  1. What is the primary purpose of the FILTER function in DAX?

    • i) To aggregate data.

    • ii) To create visualizations.

    • iii) To filter rows in a table based on conditions.

    • iv) To join multiple tables.

  2. When using FILTER within CALCULATE, what does it typically modify?

    • i) The base table for calculations.

    • ii) The aggregation function.

    • iii) The visual appearance of the report.

    • iv) The relationships between tables.

Duration: 20 minutes

Difficulty: Intermediate

Period:
This workout will be released on Wednesday, September 20, 2023, and will end on Thursday, October 05, 2023. But you can always come back to any of the workouts and solve them.

2

ANSWER

Interactive Task
1- Filter(Sales,Sales[Region] = “West”)
2- Filter(Sales,Sales[Price] > 50)
3- Sum of prices for products sold more than 100 times

Questions
1- To filter rows in a table based on conditions
2- The aggregation function

3

Hi @EnterpriseDNA

Please find my solution to this workout.

Interactive Task:

  1. Create a new table that contains only products sold in the “West” region.
  • Your Answer:
FILTER(
    'Sales',
    'Sales'[Region] = "West"
)

2) Derive a table showcasing products that were sold at a price greater than $50.

Your Answer:
FILTER(
    'Sales',
    'Sales'[Price] > 50
)

3) Given the following DAX formula, identify its output:


CALCULATE(SUM('Sales'[Price]), FILTER('Sales', 'Sales'[Quantity Sold] > 100)) 

Expected output:

ii) Sum of prices for products sold more than 100 times.

Questions:
1) What is the primary purpose of the FILTER function in DAX?
Answer:
iii) To filter rows in a table based on conditions.

2) When using FILTER within CALCULATE, what does it typically modify?
Answer:
i) The base table for calculations.

Thanks for the workout.
Keith