DAXDAX Trainer

DAX Concept

Categorize Program Viewing Figures into Bands Using SWITCH

Learn to use the DAX SWITCH function with TRUE() to categorize numerical data into predefined bands within a calculated column, enhancing data analysis and reporting.

IntermediateSWITCH

Concept Breakdown

The SWITCH function in DAX is a powerful tool for conditional logic. When used with TRUE() as its first argument, it acts like a series of IF statements, evaluating each condition sequentially until one is met. The syntax is `SWITCH(TRUE(), Condition1, Result1, Condition2, Result2, ..., ElseResult)`. This allows for creating custom categorical groupings based on numerical ranges or other criteria, making your data more digestible for reporting.

What You Will Learn

  1. Understand when to use SWITCH 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 Power BI file.

  2. Open the Power BI Desktop file.

  3. Navigate to the 'Series' table in the Data view or Model view.

  4. Create a new calculated column in the 'Series' table. Name this column 'Viewing Band'.

  5. Write the DAX formula for the 'Viewing Band' column using the SWITCH function to apply the following rules based on the 'Million viewers' column:

    • Up to and including 5 Million viewers: 'Niche'
    • 6 to 8 Million viewers: 'Mainstream'
    • 9 to 12 Million viewers: 'Cult'
    • More than 12 Million viewers: 'National treasure'

  10. Switch to the Report view and transform the existing table visual into a matrix visual.

  11. Add 'Channel' to the Rows of the matrix.

  12. Add the newly created 'Viewing Band' to the Columns of the matrix.

  13. Add a count of 'Series' (or any relevant field to count rows, e.g., 'Series'[Series ID]) to the Values of the matrix.

  14. Verify the results, observing the distribution of series across viewing bands for each channel.

  15. Save the Power BI file with the name 'There was only one Rahul'.

  16. Close the Power BI Desktop file.

Starter DAX

Viewing Band = SWITCH(TRUE(),
    // Add conditions here
    // Condition1, Result1,
    // Condition2, Result2,
    // ElseResult
)

Expected Outcome

A matrix visual displaying Channels as rows and Viewing Bands as columns, with the values showing the count of series for each channel within each band. For example, Channel 4 should have counts in 'Niche', 'Mainstream', and 'Cult' bands, but not in 'National treasure'.

Checking your sign-in status...