Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am currently trying to create a measure that captures the number of users with a certification and without certifications for each company. Some users have rows with (N/A) in the Cert course column meaning they either dont have a cert or its a filler row for their contact record. The IDs are unique to each user. Here is a sample of my data structure below.
I would like the results to look like this below:
Company Name | Contacts with Certs | Contacts without certification |
MNI | 1 | 7 |
PCT | 1 | 0 |
As you can see Mike from MNI has a row of N/A and my measure counts that as a contact without a cert so i get skewed numbers. Any guidance would be greatly appreciated! thank you all
Solved! Go to Solution.
Hi @milkmoneymike ,
Please try the following measure:
Contacts without certification =
CALCULATE (
DISTINCTCOUNT ( Table[Contact Name] ),
FILTER (
VALUES ( Table[Contact Name] ),
'N/A' IN VALUES ( Table[Cert Course] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @milkmoneymike ,
Please try the following measure:
Contacts without certification =
CALCULATE (
DISTINCTCOUNT ( Table[Contact Name] ),
FILTER (
VALUES ( Table[Contact Name] ),
'N/A' IN VALUES ( Table[Cert Course] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@milkmoneymike , You can create two measures
certification = countrows(filter(table, table[cert course] ="N/A"))
without certifications = countrows(filter(table, table[cert course] <> "N/A"))
Or you can create a new column and take count of that in matrix and use this column of matrix
new column = if(table[cert course] ="N/A", "without certifications" ,"certification")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |