cancel
Showing results for
Did you mean:
New Member

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

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!

1 ACCEPTED SOLUTION
Resolver II

@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.
2 REPLIES 2
New Member

Thanks, it works!

Resolver II

@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.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.