Forum Discussion
Count unique values
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
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.
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
AA2 A 1 AA4 A 1 AA4 B 1 AA7 B 1 AA7 B 1 AA7 A 1 AA8 A 1 AA7 A 2 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!
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?
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.
