Help with a measure

[milkmoneymike]

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

[v-deddai1-msft · Accepted 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

[amitchandak]

@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")