Check whether an ID has two specific values in another column

[joostvanham]

Hi,

Hopefully someone can help me with the following. I have a column with ID's that can have multiple values for another column. I'd like to count/filter all ID's that have a specific value for that other column. Small example:

Column with ID	Classification
A	X
B	Y
C	<other>
A	X
B	<other>
C	Y

I would like to get all ID's that have value X AND Y in column "Classification". How can I do that?

Looking forward to your suggestions!

KR,

Joost

[joostvanham · Accepted solution]

Couldn't quite make the above solutions work. It has something to do with the fact that ID's reoccur a lot. I had to pivot the table and then count for X and Y if they were > 0 for the ID's in the table. If I created a X AND Y > 0 I could get the cross section I need. Thanks all for the support!

[v-jayw-msft]

Hi @joostvanham ,

Create a measure as below and add it to visual filter to filter measure = 1 and you will get the IDs that have value X AND Y.

measure = 
var count_x = CALCULATE(DISTINCTCOUNT('Table'[Column with ID]),FILTER(ALLEXCEPT('Table','Table'[Column with ID]),'Table'[Classification]="X"))
var count_y = CALCULATE(DISTINCTCOUNT('Table'[Column with ID]),FILTER(ALLEXCEPT('Table','Table'[Column with ID]),'Table'[Classification]="Y"))
return
IF(count_x>0 && count_y>0, 1, 0)

 If you want a count of these IDs, you could create a sumx() measure.

Measure 2 = SUMX('Table',[measure])

Best Regards,

Jay

[yasemsem]

I had a similar issue and this has resolved it. Thank you so much

[Preeti_Yadav]

Hi @joostvanham 

You can try creating the following measure:

Measure =

CALCULATE (

COUNT( 'Table'[a]),FILTER('Table','Table'[b] IN {"X" ,"Y"})

)

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

Thank you

Preeti

[joostvanham]

Hi Preeti,

Thank you! Your solution gives me OR X OR Y rather than the ID's that have an occurance for both X AND Y. Any thoughts there?

Kindest regards,

Joost

[Preeti_Yadav]

Hi @joostvanham 

As per your requirement you might try this :

Measure =

CALCULATE (

COUNT( 'Table'[a]),FILTER('Table','Table'[b] ="X" || 'Table'[b]="Y")

)

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

Thank you

Preeti

[Fowmy]

@joostvanham 

Create this measure:

Count = 

CALCULATE (
    COUNT( TABLE[ID]),
    TABLE[Classification] IN {"X","Y"} 
)

[joostvanham]

Hi Fowmy,

Thank you! Your solution gives me OR X OR Y rather than the ID's that have an occurance for both X AND Y. Any thoughts there?

Kindest regards,

Joost

[Fowmy]

@joostvanham 

Based on your sample data, my calculation will give you a result of 4 as it counts x and y.  
If you need the distinct count then you can change the COUNT to DISTINCTCOUNT

can you share the expected result and how it should be calculated otherwise ?