cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## 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
Super Contributor

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

Hello @Anonymous

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

Proud to be a Datanaut!

Super Contributor

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

Hello @Anonymous

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

Proud to be a Datanaut!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 147 members 1,497 guests
Recent signins: