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, ...
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

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

      • Cactus26's avatar
        Cactus26
        Helper I
        8 years ago

        Hi Anonymous,

         

        I wanted to upload the sample file from PowerBI but is impossible.So, I will put screenshots then, it will be easier to explain.

         

        Raw table:

        Test      Country       Week

        AA2A1
        AA4A1
        AA4B1
        AA7B1
        AA7B1
        AA7A1
        AA8A1
        AA7A2

         

        Measures:

         

        Measure_1 = if(CALCULATE(COUNTA('Table'[Test])<=1),1,0)

        Measure_2 = if(CALCULATE(COUNTA('Table'[Test])>=3),1,0)

        Measure_3 = [Measure_2]/[Measure_1]

         

        Basically, I need to create measures which will be dynamically changed after filters in slicers will be applied (week, country and many more not mentioned), so it is why I cannot make it as a calculated column as the values will be changed regardless of the filters. Using mentioned solution by you I am getting something like:

         

         

        Here no filters where chosen. So I am expecting in Measure_1 for Total: 2 but is 0 and Measure_3 equal 0.5 but is infinity.

        Measure_1 has to count the unique values (which has no duplicates) and this measure will be represented as KPI, as one value

        Measure_2 has to count the number of values which occured 3 or more times.

         

        Maybe you are right but sill I am not satisifed with the results and perhaps I do not understand it as I am still learning.

         

        Thanks a lot!