Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is developing a Power BI model for a manufacturing company. The model contains a 'Production' fact table and a 'Machines' dimension table. The 'Machines' table has a 'MaintenanceDate' column that signifies the last maintenance performed. The analyst needs to calculate the total production for machines that have not had maintenance in the last 90 days, based on the current date selected in the report. Which DAX pattern should be used to identify these machines?
- AUse a measure with CALCULATE and FILTER, comparing 'MaintenanceDate' to TODAY() - 90, and then SUM 'Production'.
- BCreate a calculated column in 'Machines' to determine 'DaysSinceMaintenance' and filter on it.
- CUse DATESBETWEEN to filter 'MaintenanceDate' and then CALCULATE SUM of production.
- DUse FILTER with EARLIER to compare 'MaintenanceDate' with the current date minus 90 days.
Show answer & explanationAnswer & explanation
Correct answer: A. Use a measure with CALCULATE and FILTER, comparing 'MaintenanceDate' to TODAY() - 90, and then SUM 'Production'.
Using a measure with CALCULATE and FILTER allows for dynamic filtering based on the current date (TODAY()) and calculating the sum of production. A calculated column would be static and not respond to the 'current date selected in the report' requirement.
Why the other options are wrong
- B. Calculated columns are static and would not update based on the 'current date selected in the report'.
- C. DATESBETWEEN is typically for date ranges in a date dimension, not for comparing an individual date to a dynamic threshold within a dimension table.
- D. EARLIER is used for row context transitions within iterating functions, not directly for filtering a dimension table based on a dynamic date range.
Dynamic Date Filtering in Measures
Applying date-based filters within a DAX measure that adjust based on a dynamic 'current date' (e.g., TODAY() or a selected date in a slicer).
- Uses CALCULATE and FILTER functions.
- Compares a date column to a dynamic date expression (e.g., TODAY() - 90).
- Ensures calculations reflect the current context or system date.
Memory trick: Today's date minus time, then filter and calculate.