Forum Discussion
Check whether an ID has two specific values in another column
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 ...
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!
Hi Anonymous ,
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
I had a similar issue and this has resolved it. Thank you so much
