Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
} )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |