Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
Hello,
I am new to Dax and I need some help in getting an optimized way to create these measures. When I use these measures it takes a longer time to get the result
First measure=CALCULATE(
DISTINCTCOUNT(factInspectionViolation[InspectionKey]),
FILTER( 'dimInspection', dimInspection[InspectionLevelNumber] IN {3,2,1} )
)
Second Measure =
CALCULATE(
DISTINCTCOUNT(factInspectionViolation[InspectionKey]),
FILTER ( factInspectionViolation, [IsOutOfService] = "Y" ),
FILTER( dimViolation, [BASIC] IN { "Driver Fitness", "Hours of Service Compliance", "Controlled Substances / Alcohol" }
))
Third Measure=DIVIDE([SecondMeasure],[First Measuer])
Solved! Go to Solution.
See if it help to filter columns rather than tables.
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
FILTER (
VALUES ( 'dimInspection'[InspectionLevelNumber] ),
dimInspection[InspectionLevelNumber] IN { 3, 2, 1 }
)
),
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
FILTER (
VALUES ( factInspectionViolation[IsOutOfService] ),
factInspectionViolation[IsOutOfService] = "Y"
),
FILTER (
VALUES ( dimViolation[BASIC] ),
dimViolation[BASIC]
IN {
"Driver Fitness",
"Hours of Service Compliance",
"Controlled Substances / Alcohol"
}
)
)
)
It's a bit cleaner-looking like this:
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
KEEPFILTERS ( dimInspection[InspectionLevelNumber] IN { 3, 2, 1 } )
),
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
KEEPFILTERS ( factInspectionViolation[IsOutOfService] = "Y" ),
KEEPFILTERS ( dimViolation[BASIC]
IN {
"Driver Fitness",
"Hours of Service Compliance",
"Controlled Substances / Alcohol"
} )
)
)
See if it help to filter columns rather than tables.
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
FILTER (
VALUES ( 'dimInspection'[InspectionLevelNumber] ),
dimInspection[InspectionLevelNumber] IN { 3, 2, 1 }
)
),
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
FILTER (
VALUES ( factInspectionViolation[IsOutOfService] ),
factInspectionViolation[IsOutOfService] = "Y"
),
FILTER (
VALUES ( dimViolation[BASIC] ),
dimViolation[BASIC]
IN {
"Driver Fitness",
"Hours of Service Compliance",
"Controlled Substances / Alcohol"
}
)
)
)
It's a bit cleaner-looking like this:
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
KEEPFILTERS ( dimInspection[InspectionLevelNumber] IN { 3, 2, 1 } )
),
CALCULATE (
DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
KEEPFILTERS ( factInspectionViolation[IsOutOfService] = "Y" ),
KEEPFILTERS ( dimViolation[BASIC]
IN {
"Driver Fitness",
"Hours of Service Compliance",
"Controlled Substances / Alcohol"
} )
)
)
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
| User | Count |
|---|---|
| 67 | |
| 45 | |
| 43 | |
| 36 | |
| 23 |
| User | Count |
|---|---|
| 191 | |
| 127 | |
| 106 | |
| 78 | |
| 53 |