Concept Breakdown
The FILTER function is a table function that returns a subset of a table or another expression's result. When combined with an iterator function like SUMX, it allows for calculating expressions over specific rows. Multiple conditions can be combined within FILTER using logical operators like AND (or the && operator) to define precise criteria for row selection before the calculation takes place.
What You Will Learn
Understand when to use SUMX, FILTER, YEAR, AND (or && operator) in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a beginner-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided Power BI file.
Open the Power BI file in the folder.
Create a new measure called `Sales` that sums the `Price` multiplied by the `Quantity` from the 'Purchase' table. (e.g., `SUMX('Purchase', 'Purchase'[Quantity] * 'Purchase'[Price])`).
Add the `Sales` measure to the matrix visual to see total sales by purchase year and environment.
Copy the `Sales` measure formula and create a new measure called `Sales in 2018`. Modify this measure to use the `FILTER` function to only sum sales where the year of the `PurchaseDate` is 2018 (e.g., `YEAR('Purchase'[PurchaseDate]) = 2018`).
Replace the `Sales` measure in the matrix with `Sales in 2018`.
Copy the `Sales in 2018` formula and create a new measure called `Big sales 2018`. Amend this measure to include a second filtering condition: the `Quantity` of goods bought for a purchase must be 5 or more. Use the `AND` function or the `&&` operator to combine the conditions.
Replace `Sales in 2018` in the matrix with `Big sales 2018`.
Save the file as 'Special measures'.
Starter DAX
Sales = SUMX('Purchase', 'Purchase'[Quantity] * 'Purchase'[Price])Expected Outcome
A matrix showing total sales by purchase year and environment. Initially, total sales will be displayed. Subsequent measures will filter the data: first showing only sales for 2018, and then further refining to show sales for 2018 where the quantity was 5 or more. Each successive filter will result in lower total figures in the matrix.