DAXDAX Trainer

DAX Concept

Conditional Logic in Calculated Columns: IF, SWITCH, ISBLANK, AND

Learn to apply conditional DAX functions such as IF, SWITCH, ISBLANK, AND, and OR to create descriptive calculated columns, enhancing data categorization and reporting in Power BI.

IntermediateIFSWITCHISBLANKANDOR

Concept Breakdown

Conditional functions in DAX enable dynamic logic, allowing you to classify data, handle nulls, and evaluate multiple criteria within your calculations. IF provides a simple binary choice, SWITCH handles multiple conditions more efficiently than nested IFs, ISBLANK identifies missing values, and AND/OR (or their operators &&/||) combine logical tests to form complex conditions.

What You Will Learn

  1. Understand when to use IF, SWITCH, ISBLANK, AND, OR 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 file 'Music Tours - Conditional Functions.pbix'.

  2. Open the 'Music Tours - Conditional Functions.pbix' file in Power BI Desktop and navigate to the Table view of the 'Tour' table.

  3. Add a new calculated column named 'Duration'.

  4. Use an IF function to test if the 'Start year' is the same as the 'End year'. If true, return "Single year"; otherwise, return "Multi year".

  5. Create a matrix visual to display the count of tours for each 'Duration' category.

  6. Add a second calculated column named 'Crowd size'.

  7. Use a SWITCH function to describe the 'Average attendance' based on the following: 'Unknown' if blank, 'Small' if less than 25,000, 'Medium' if 25,000 to less than 50,000, and 'Large' if 50,000 or more.

  8. Use the new 'Crowd size' column in a slicer visual to filter a table displaying tour details.

  9. Add a third calculated column named 'Success'.

  10. Use a SWITCH function along with AND/OR to describe each tour according to these criteria: 'Smash hit' if 'Actual gross' is at least 200,000,000 AND 'Attendance' is at least 2,000,000; 'Modest success' if 'Actual gross' is at least 200,000,000 OR 'Attendance' is at least 2,000,000; otherwise, 'Abject failure'.

  11. Use the new 'Success' column in a matrix visual to count the tours for each category. You can optionally use the 'Crowd size' slicer to filter the results.

  12. Save and close the Power BI file.

Starter DAX

New Calculated Column = 
// Your conditional DAX logic here
// Example: IF([Value] > 100, "High", "Low")

Expected Outcome

Three new calculated columns ('Duration', 'Crowd size', 'Success') will be added to the 'Tour' table, categorizing tours based on conditional logic. Visualizations will display tour counts by duration, allow interactive filtering by crowd size, and present tour counts grouped by success level.

Checking your sign-in status...