Forum Discussion
Helper IIDistinct Count If Multiple Criteria
Not sure if I'm trying to do this the hard way, but I have a table has a row for each training a person goes through, I want to count the number of Yellow Belts, Green Belts and Black Belts, but don't want to double count.
The Employee Name tells me the employee, the activity name tells me the training they went through. So the table looks like this:
| User Name | Activity Name |
| A | DMAIC Yellow Belt Certification |
| A | Lean Yellow Belt Certification |
| A | DMAIC Green Belt Certification |
| A | ABC Training |
| A | XYZ Training |
| B | Lean Yellow Belt Certification |
| B | ABC Training |
| B | XYZ Training |
In the case above, I would want it to return the following Counts:
Yellow Belt Training = 2 (User A and B both went through at least 1 Yellow Belt Certification)
Green Belt Training = 1 (User A is the only one who has any Greenbelt Training).
I've been able to create a measure that shows Yellow Belt/Green Belt Certifications, but can't get them to Count/Sum and am not sure how to make sure they don't double count by user:
Screenshot below shows user ID instead of Employee Name mostly for privacy reasons - either can be used:
Table Set Up
Hi DemingPDCA,
Create flags in your table for the different belts.
Yellow = IF(CONTAINSSTRING('Table (2)'[Activity Name],"Yellow Belt"),1,0)Summarize this table to have count of individual belts for every person.
Result = SUMMARIZE('Table (2)','Table (2)'[User Name], "Yellow",SUM('Table (2)'[Yellow]), "Green",SUM('Table (2)'[Green]))Now you can use the columns from this result table to create meaures that count and sum.
Yellow Count = CALCULATE(COUNT(Result[Yellow]),FILTER(Result,Result[Yellow]>0))Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI
2 Replies
- Shaurya
Memorable Member
Hi DemingPDCA,
Create flags in your table for the different belts.
Yellow = IF(CONTAINSSTRING('Table (2)'[Activity Name],"Yellow Belt"),1,0)Summarize this table to have count of individual belts for every person.
Result = SUMMARIZE('Table (2)','Table (2)'[User Name], "Yellow",SUM('Table (2)'[Yellow]), "Green",SUM('Table (2)'[Green]))Now you can use the columns from this result table to create meaures that count and sum.
Yellow Count = CALCULATE(COUNT(Result[Yellow]),FILTER(Result,Result[Yellow]>0))Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI
DemingPDCAThis worked great - thanks!