Forum Discussion

Cactus26's avatar
Cactus26
Helper I
8 years ago
Solved

Count unique values

Hi,

 

I am struggling with creation of the measure which will be recalculated after the filters in report via slicers will be selected.

I was able to get the same effect using calculated columns, however I had to hard code the filters in formulas which I wanted to use.

 

I need two measures which will calculate from the one column (string):

1. number of unique values but count only those which are available in column just one time

2. number of values which were repeated 3 or more times in column

 

Let's say, in colum A we store following information:

 

AA2

AA4

AA4

AA7

AA7

AA7

AA8

AA7

 

For 1 measure I will get: 2 (because just AA2 and AA8 are unique and not repeated)

For measure 2 I will get:  1 (because just AA7 is repeated 3 or more times)

 

Thanks a lot!

Need Help
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    Hi,

     

    Something like this should work (did not test it, writing it blind)

    COUNTROWS ( 
    FILTER ( 
        VALUES ( Table[YourColumn] ),
        CALCULATE ( COUNTROWS ( Table ) = 1
     )
)

and 

COUNTROWS ( 
    FILTER ( 
        VALUES ( Table[YourColumn] ),
        CALCULATE ( COUNTROWS ( Table ) >= 3
     )
)


    Of course, by playing with the condition of FILTER, you can achievev different results. 


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

10 Replies

  • AlbertoFerrari's avatar
    AlbertoFerrari
    Most Valuable Professional
    8 years ago

    Hi,

     

    Something like this should work (did not test it, writing it blind)

    COUNTROWS ( 
    FILTER ( 
        VALUES ( Table[YourColumn] ),
        CALCULATE ( COUNTROWS ( Table ) = 1
     )
)

and 

COUNTROWS ( 
    FILTER ( 
        VALUES ( Table[YourColumn] ),
        CALCULATE ( COUNTROWS ( Table ) >= 3
     )
)


    Of course, by playing with the condition of FILTER, you can achievev different results. 


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    hi Cactus26,

     

    Does this solve your issue..

     

    Count Measure_1 =if(COUNT(Test[test])<=1,count(Test[test]),blank())

    Count Measure_2 =if(COUNT(Test[test])<=3,count(Test[test]),blank())

     

      I have put your test data in a table name Test & in a column in it name test.

     

    Regards

    • Cactus26's avatar
      Cactus26
      Helper I
      8 years ago

      Hi Anonymous,

       

      Unfortunately it did not work :/ I am just receving blank as a value.

      I have also tried with COUNTA as there are string in this column but the same.

      I am out of ideas how it can be solved.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI Cactus26

         

          It will work you need to bring both measure & the column on a table visual.

         

        Regards

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this

     

    Measure 1

     

    =COUNTROWS(FILTER(VALUES(Data[Data]),COUNTA([Data])=1))

    Measure 2

     

    =COUNTROWS(FILTER(VALUES(Data[Data]),COUNTA([Data])>=3))