Count measure by groups

[mizaskun]

Good morning,

Can anybody help me with this measure please?

We sent a questionaire to some people of the company. Each question have 3 categories to be answered. A, B and C. Each category must be answered with a number. There is also a status field in that table with which we see if each cateogry of each question was correctly answered.

I need to come up with 2 measures: OK and Warning.

OK should be the number of questions correctly answered, which means that the 3 categories are with an OK. In this example only the question 3 was correctly answered.

Warning should be the number of questions with one warning along its answers. Questions number 1 and 2 should be warnings.

Thea measures should be:

• OK: 1
• Warning: 2

Thank you so much! 

[PaulOlding]

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

[mizaskun]

Thank you! It should be warning. I will try it! As soon as I check it I will accept as a solution!