Forum Discussion
Count measure by groups
What results do you want if someone sliced by Category?
Let's say there's a slicer on the page with just Category B selected. Should question 2 now be in OK or Warning?
These measure would count it as Warning (thanks to the ALLEXCEPT function. You could take that bit out and Q2 would now be OK in the scenario above).
OK Count =
VAR _QsWithCounts =
ADDCOLUMNS(
VALUES('Table'[Question]),
"@AllCount", CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Question])),
"@OKCount", CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "OK", ALLEXCEPT('Table', 'Table'[Question]))
)
VAR _Result = COUNTROWS(FILTER(_QsWithCounts, [@OKCount] = [@AllCount]))
RETURN
_Result
Warning Count =
VAR _QsWithCounts =
ADDCOLUMNS(
VALUES('Table'[Question]),
"@AllCount", CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Question])),
"@OKCount", CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "OK", ALLEXCEPT('Table', 'Table'[Question]))
)
VAR _Result = COUNTROWS(FILTER(_QsWithCounts, [@OKCount] <> [@AllCount]))
RETURN
_Result
Thank you! It should be warning. I will try it! As soon as I check it I will accept as a solution!