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

CahabaData's avatar
CahabaData
Memorable Member
8 years ago

Your post is a little unclear/confusing to me.  To clarify;

i.e. If the slicer selection is Week 2 - then your Table Visual will display just 1 row AA7 ... and therefore a COUNTROW measure is = 1

 

is this correct?

 

 

Cactus26's avatar
Cactus26
Helper I
8 years ago

Hi CahabaData

 

Generally I am not interested in showing Table Visual but to visualise one number for each measure not like  break down per each item like I showed in the screenshot.

 

Let's say when we filter week 2, only item AA7 will be filtered, so measure_1=1 (because AA7 is unique after filter set), measure_2=0(because there is just one item AA7 and for this measure I am interested in items which are repeated 3 or more times) and measure_3=0.

 

But let presume that we filter data and we have situation where such items are listed: AA2, AA7, AA7, AA7, AA7, AA3, then measure_1=2 (because AA2 and AA3 are unique), measure_2=1(becasue AA7 is repeated 4 times) and measure_3=1/2

 

I was thinking if we should create some variables to make it works correctly.

 

In calculated column I could do like this but unfortunatelty those numbers are static in column:

IF(
CALCULATE(
COUNTA('Table'[Test]);
FILTER('Table'; 'Table'[Test] = EARLIER('Table'[Test]))
)>=3;
1;
0
)

 

I hope that it more clear now.

 

  • Cactus26's avatar
    Cactus26
    Helper I
    8 years ago

    Anonymous

     

    Is there a way to get one number of your proposition per each measure? It works just when it has reference to items in table but I need the total count for measure_1 and measure_2

     

    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 tried like this:

    Measure_1 = SUMX('Table';IF(CALCULATE(COUNTA('Table'[Test]); FILTER('Table';COUNTA('Table'[Test])<=1))=0;0;1)) but it does not sum