Forum Discussion
msprog
6 years agoAdvocate III
Help with DAX please
Hello, I am looking for some help in PowerBI. We have 5 string measures called P1IncidentResolutionGrade, P2IncidentResolutionGrade, P3IncidentResolutionGrade, P4IncidentResolutionGrade, P5Incident...
- 6 years ago
Hi msprog
Create measures
count of fail = VAR p1_f = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Fair" ) ) VAR p2_f = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Fair" ) ) VAR p3_f = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Fair" ) ) VAR p4_f = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Fair" ) ) VAR p5_f = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Fair" ) ) RETURN p1_f + p2_f + p3_f + p4_f + p5_f count of good = VAR p1_g = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Good" ) ) VAR p2_g = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Good" ) ) VAR p3_g = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Good" ) ) VAR p4_g = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Good" ) ) VAR p5_g = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Good" ) ) RETURN p1_g + p2_g + p3_g + p4_g + p5_g count of excellent = VAR p1_e = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Excellent" ) ) VAR p2_e = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Excellent" ) ) VAR p3_e = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Excellent" ) ) VAR p4_e = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Excellent" ) ) VAR p5_e = CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Excellent" ) ) RETURN p1_e + p2_e + p3_e + p4_e + p5_e final measure = IF ( [Measure p1] = "Poor" || [Measure p2] = "Poor" || [Measure p3] = "Poor" || [Measure p4] = "Poor" || [Measure p5] = "Poor", "Poor", IF ( [count of fail] >= 3, "Fail", IF ( [count of good] >= 4, "Good", IF ( [count of excellent] = 5, "Excellent" ) ) ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft
6 years agoCommunity Support
Hi msprog
Create measures
count of fail =
VAR p1_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Fair" ) )
VAR p2_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Fair" ) )
VAR p3_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Fair" ) )
VAR p4_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Fair" ) )
VAR p5_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Fair" ) )
RETURN
p1_f + p2_f + p3_f + p4_f + p5_f
count of good =
VAR p1_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Good" ) )
VAR p2_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Good" ) )
VAR p3_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Good" ) )
VAR p4_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Good" ) )
VAR p5_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Good" ) )
RETURN
p1_g + p2_g + p3_g + p4_g + p5_g
count of excellent =
VAR p1_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p1] = "Excellent" )
)
VAR p2_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p2] = "Excellent" )
)
VAR p3_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p3] = "Excellent" )
)
VAR p4_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p4] = "Excellent" )
)
VAR p5_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p5] = "Excellent" )
)
RETURN
p1_e + p2_e + p3_e + p4_e + p5_e
final measure =
IF (
[Measure p1] = "Poor"
|| [Measure p2] = "Poor"
|| [Measure p3] = "Poor"
|| [Measure p4] = "Poor"
|| [Measure p5] = "Poor",
"Poor",
IF (
[count of fail] >= 3,
"Fail",
IF (
[count of good] >= 4,
"Good",
IF ( [count of excellent] = 5, "Excellent" )
)
)
)
Best Regards
Maggie
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.