Generate a report to calculate number of items that meet multiple criterias

[thuhanoi]

I have a list of participants who attend the different training in different Quarter like this:

And I want to have report to show how many participants attends by quarter, how many passed (just passed 1 training session is counted), and how many passed both training session 1 and 2. The report like this:

Anyone knows how to do it as I got stuck to get number who passed both session 1 and 2, as well as to get the total number with removing duplicate (participants can attend many training session, but when we count, we just count as 1 participant).

Thanks!

[thuhanoi]

Thanks, it works!

[colacan · Accepted solution]

@thuhanoi  Hi,  you may try below 2 measures.

To count (distinct) participant passed the test:

CountPass = CALCULATE(

DISTINCTCOUNT(TestReport[Name]),

TestReport[TestResult] = "Passed"

)

To count (distinct) participant passed both Session1 and Session2:

BothPassed =

VAR s1Passed =

SELECTCOLUMNS(

FILTER(

TestReport,

TestReport[Training] = "S1" && TestReport[TestResult] = "Passed"

),

"TestPassed", TestReport[Name]

)

VAR s2Passed =

SELECTCOLUMNS(

FILTER(

TestReport,

TestReport[Training] = "S2" && TestReport[TestResult] = "Passed"

),

"TestPassed", TestReport[Name]

)

VAR bothpassed =

DISTINCT(NATURALINNERJOIN( s1Passed, s2Passed ))

RETURN

COUNTROWS(bothpassed)

Thanks.

Please mark it as solution if this helped.