Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 that other column. Small example:

 

Column with IDClassification
AX
BY
C<other>
AX
B<other>
CY

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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!

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • yasemsem's avatar
      yasemsem
      Frequent Visitor
      3 years ago

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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Create this measure:

    Count = 

CALCULATE (
    COUNT( TABLE[ID]),
    TABLE[Classification] IN {"X","Y"} 
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        Anonymous 

         

        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 ?

  • Preeti_Yadav's avatar
    Preeti_Yadav
    Icon for Resolver I rankResolver I
    5 years ago

    Hi Anonymous 

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Preeti_Yadav
        Icon for Resolver I rankResolver I
        5 years ago

        Hi Anonymous 

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!