Concept Breakdown
The FILTER function is an iterator that evaluates a Boolean expression for each row of a table or an expression that returns a table. It returns a table containing only those rows for which the expression is TRUE. This virtual table can then be passed to other functions, such as aggregation functions or CALCULATE, to perform complex calculations based on a derived set of data. When used as a filter argument within CALCULATE, FILTER can modify the filter context, allowing measures to aggregate data not just on direct column values but also on conditions involving related tables or complex logic.
What You Will Learn
Understand when to use FILTER, CALCULATE, COUNTROWS in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a intermediate-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 'Music Tours - More Filter Functions.pbix' file.
Open the 'Music Tours - More Filter Functions.pbix' file.
Navigate to Page 1 of the report.
Create a new measure named 'COVID Cancelled Shows' that counts the number of shows cancelled due to COVID. The 'Cancellation reason' column in the 'Shows' table can be checked for the word 'COVID'.
Create a measure named 'Tours With COVID Cancelled Shows' which counts the tours that had at least one show cancelled due to COVID. Combine the COUNTROWS and FILTER functions for this.
Display the 'Tours With COVID Cancelled Shows' measure in the existing table and in a separate card visual (e.g., a New Card visual).
Create two new measures for tours that had at least one show cancelled due to COVID: 'Total Shows in COVID Tours' to count the rows of the 'Show' table and 'Total Tickets Sold in COVID Tours' to sum the 'Tickets sold' column. Combine CALCULATE and FILTER functions for these.
Show the results of 'Total Shows in COVID Tours' and 'Total Tickets Sold in COVID Tours' in the table and card visuals.
Navigate to Page 2 of the report.
Create a measure named '#1 Tracks' to count the tracks which reached number one in the charts, using the 'US Billboard Hot 100 peak' column.
Create another measure named 'Albums with #1 Tracks' to count albums that have at least one number one track.
Display '#1 Tracks' and 'Albums with #1 Tracks' in the table.
For albums with at least one number one track, create the following measures:
a. 'Total Tracks from #1 Albums': Count of all tracks.
b. 'Total Track Length from #1 Albums': Sum of track length for all tracks.
c. 'Average Peak Position': Average peak chart position of all tracks.
d. 'Average Peak Position in #1 Albums': Average peak chart position of tracks from these albums.
Display these new measures in the table.
Save and close the report.
Starter DAX
COVID Cancelled Shows =
COUNTROWS(
FILTER(
'Shows',
CONTAINSSTRING('Shows'[Cancellation reason], "COVID")
)
)Expected Outcome
The tables on Page 1 and Page 2 will display the newly created measures, showing counts and aggregations for COVID-cancelled tours and number-one albums/tracks, respectively. Card visuals will separately display key aggregate values like total tours with COVID cancellations and total shows/tickets from those tours.