DAXDAX Trainer

DAX Concept

Filtering Tables with the DAX FILTER Function for Dynamic Calculations

Understand and apply the DAX FILTER function to create filtered tables for other functions, enabling dynamic calculations based on specific conditions.

IntermediateFILTERSUMXCOUNTROWSCONTAINSSTRINGRELATED

Concept Breakdown

The FILTER function in DAX is a table function that returns a table (or a column from a table) that has been filtered according to the specified filter conditions. It doesn't return scalar values directly; instead, it provides a 'filtered version' of a table that can then be processed by other DAX functions (like iterators such as SUMX, or aggregation functions like COUNTROWS) to produce a scalar result. This allows for powerful context transitions and dynamic calculations.

What You Will Learn

  1. Understand when to use FILTER, SUMX, COUNTROWS, CONTAINSSTRING, RELATED in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. 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.

  1. Download and unzip the provided 'Music Tours - Filter Function.pbix' file and open it in Power BI Desktop.

  2. Observe the existing table visual showing total tickets sold by each artist.

  3. Create a new measure named 'Total Tickets Sold for Sold-Out Shows' using SUMX and FILTER to calculate the sum of 'Tickets sold' only for shows where 'Tickets available' equals 'Tickets sold'. Add this measure to the table visual.

  4. Create a new measure named 'Cancelled Shows Count' using COUNTROWS and FILTER to count shows where the 'Cancelled' column contains 1. Add this measure to the table visual.

  5. Create another measure named 'COVID Cancelled Shows' using COUNTROWS, FILTER, and CONTAINSSTRING to count shows that were cancelled (Cancelled = 1) AND whose reason mentions 'COVID'. Add this measure to the table visual.

  6. Create a measure named 'Spain or Bullring Shows' using COUNTROWS, FILTER, and RELATED to count shows where the 'Country' (from 'Venues' table) is 'Spain' OR the 'Venue Type' (from 'Venues' table) is 'Bullring'. Add this measure to the table visual.

  7. Verify the results in the table visual against the provided sample results if available. Save and close the report.

Starter DAX

Tickets Sold Out = SUMX( FILTER( 'Tour Shows', <condition> ), 'Tour Shows'[Tickets sold] )

Expected Outcome

The table visual will display new columns for each artist, showing the total tickets sold for sold-out shows, the count of cancelled shows, the count of COVID-related cancelled shows, and the count of shows held in Spain or a Bullring venue.

Checking your sign-in status...