Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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"
} )
)
)
User | Count |
---|---|
60 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
40 | |
39 |