Forum Discussion
Help with a measure
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
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
2 Replies
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")
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
