Concept Breakdown
The CALCULATE function is one of the most powerful and frequently used functions in DAX. It evaluates an expression in a modified filter context. You can pass one or more filters as arguments after the expression. Each filter modifies or overrides the existing filter context for the evaluation of the expression. When multiple filters are provided, they are combined with a logical AND by default. To combine filters with a logical OR, you use the '||' operator within a single filter argument.
What You Will Learn
Understand when to use CALCULATE in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a beginner-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 file, then open 'Tallest Buildings - Calculate Basic Filters.pbix' in Power BI Desktop.
Locate the matrix visual which currently displays countries and the 'Count Buildings' measure.
Create a new measure in the 'Building Measures' table called `Count Old Buildings`. Use the CALCULATE function to count buildings where the 'Year Opened' field is less than 2000.
Hint: CALCULATE ( Expression, Filter )*
Create another new measure called `% Old Buildings`. This measure should divide `Count Old Buildings` by `Count Buildings`.
Add both `Count Old Buildings` and `% Old Buildings` to the matrix visual. Apply appropriate formatting (e.g., percentage format for `% Old Buildings`).
Create a new measure named `Count Old Tall Buildings`. Use the CALCULATE function to apply two filters:
* Filter 1: 'Year Opened' is less than 2000.
* Filter 2: 'Height m' is greater than or equal to 400.
Remember: Multiple filters in CALCULATE are comma-separated.*
Display `Count Old Tall Buildings` in the matrix visual.
Modify the `Count Old Tall Buildings` measure to change the second filter. It should now check if 'Height m' is greater than or equal to 400 OR 'Floors above ground' is greater than or equal to 100.
Use the '||' operator for the OR condition.*
Check the results of your changes in the matrix visual.
Save and close the file.
Starter DAX
Count Buildings = COUNTROWS('Buildings')Expected Outcome
The matrix visual will display additional columns showing the count of buildings opened before 2000, their percentage relative to all buildings, and the count of buildings that are both old and tall (with height OR floor conditions). You should observe how applying different filter combinations within CALCULATE alters the measure results.