Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to Take Count and Distinct Count Dependent on Column Value

In my data set I need to take a count or distinct count of a unqiue ID depending on a column value.  In the below example, if the Value is A or C I need to take a count of the Unique ID and if the Value is B I need to take a distinct count.  The Expected Result is what I would need the data to be represented as in a single visualization.  Any help would be greatly appreciated as I keep going in circles.

 

Data Example  
    
ValueIDUnique IDCount Type
A12345A12345Count
A12345A12345
A12345A12345
B13579B13579Distinct
B13579B13579
B54321B54321
B54321B54321
B97531B97531
C11223C11223Count
C99887C99887

 

Expected Result
  
ValueTotal
A3
B3
C2
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    I took what you provided and was able to tweak it a little to get what I needed.  Here is where I ended up at when creating the measure.  Thanks for the assistance.

     

    Count =
    SWITCH( SELECTEDVALUE('Table'[Value]),
    "A", COUNT('Table'[Unique ID]),
    "B", DISTINCTCOUNT( 'Table'[Unique ID]),
    "C", COUNT('Table'[Unique ID]))
     
    Regards,
    Eric

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous 

     

    You can try 
    _count = 
    Switch(TRUE(),

                'A',COUNT(COL),

                'B',DISTINCTCOUNT(COL),

                'C',COUNT(COL))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      I am able to create a measure with the formula suggested, but when adding the measure to a visual I receive the following error "Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

       

      Regards,

      Eric

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Use matrix chart instead of table chart

    Or

    Create is as a column instead of measure.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      I took what you provided and was able to tweak it a little to get what I needed.  Here is where I ended up at when creating the measure.  Thanks for the assistance.

       

      Count =
      SWITCH( SELECTEDVALUE('Table'[Value]),
      "A", COUNT('Table'[Unique ID]),
      "B", DISTINCTCOUNT( 'Table'[Unique ID]),
      "C", COUNT('Table'[Unique ID]))
       
      Regards,
      Eric