Concept Breakdown
The CROSSFILTER function in DAX allows you to override the default filter direction of a specified relationship (or deactivate it) within the context of a CALCULATE or CALCULATETABLE function. This is critical when standard relationship behavior prevents desired calculations, enabling filters to flow in a 'Both' direction or to be ignored ('None') to achieve specific analytical requirements.
What You Will Learn
Understand when to use CROSSFILTER 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 'Music Tours - Cross Filter.pbix' file.
Open the `.pbix` file in Power BI Desktop.
Observe the existing table visual, which displays the number of shows at different venues, sorted by show count in descending order.
Create a new measure to count the number of tours that visited each venue. You will need to use the `CROSSFILTER` function to modify the direction of the relationship between the `Tour` and `Show` tables, specifically from `Tour[TourID]` to `'Show'[TourID]`, making it filter in 'Both' directions.
Create a second measure to show the percentage of tours that visited each venue, based on your previously created 'Number of Tours Visited' measure.
Add these two new measures to the table visual.
Create two more measures: one for the number of artists who visited each venue, and another for the percentage of artists who visited each venue. For these, you will need to use the `CROSSFILTER` function twice to modify the filter direction of two relationships (e.g., Show to Tour, and Tour to Artist) to allow filtering to flow from the `Venue` table through to the `Artist` table.
Add these two additional measures to the table visual.
Sort the table in descending order by the '% Artists' measure.
Save and close the report.
Starter DAX
Number of Tours = CALCULATE(DISTINCTCOUNT(Tour[TourName]))Expected Outcome
A table visual displaying venues, sorted by '% Artists' in descending order. The table should include columns for: Number of Shows (pre-existing), Number of Tours Visited, % Tours Visited, Number of Artists Visited, and % Artists Visited, correctly aggregated per venue.