cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
benfurphy Frequent Visitor
Frequent Visitor

Getting Distinct Count for only certain values based on values in another column

Hello all,

 

I have a table that looks something along the lines of this:

 

ID                Status Number

100                        1

100                        2

100                        1

101                        1

101                        6

101                        2

101                        1

105                        2

105                        2

203                        7

203                        1

323                        5

323                        1

445                        5

445                        2

489                        1

512                        2

 

What I want to do is create a measure that counts the number of distinct IDs that have at least 1 value that is not equal to 2, 5, or 7. So the output for this small table would be 5 since it would count 100, 101, 203, 323 and 489. It would not count 105, 445, or 512 since the only values that it has are 2, 5, or 7.

 

Thank you in advance for the help

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Getting Distinct Count for only certain values based on values in another column

Hello @benfurphy 

Here is a measure that should do what you want:

Distinct IDs Status not 2,5,7 =
CALCULATE (
    DISTINCTCOUNT ( YourTable[ID] ),
    NOT YourTable[Status Number] IN { 2, 5, 7 }
)

Does that work for you?

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
OwenAuger Super Contributor
Super Contributor

Re: Getting Distinct Count for only certain values based on values in another column

Hello @benfurphy 

Here is a measure that should do what you want:

Distinct IDs Status not 2,5,7 =
CALCULATE (
    DISTINCTCOUNT ( YourTable[ID] ),
    NOT YourTable[Status Number] IN { 2, 5, 7 }
)

Does that work for you?

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 230 members 2,233 guests
Please welcome our newest community members: