cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thuhanoi
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:

thuhanoi_0-1633501331762.png

 

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:

 

thuhanoi_2-1633501523626.png

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
colacan
Resolver II
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.

View solution in original post

2 REPLIES 2
thuhanoi
New Member

Thanks, it works!

colacan
Resolver II
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.

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors