Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a, simplified, dataset like below:
Table: Validation
ID Package Value
122 1 1
122 2 1
122 3 0
122 4 1
24 1 1
24 2 1
24 3 1
24 4 1
I require a measure to be able to count the DISTINCT IDs filtering out any IDs that has a value of 0 amongst its entries.
So for the above example the measure count would equal 1 as it only ID 24 that has all its entries having a value of 1. ID 122 has a single entry containg the value 0 and therefore that ID (122) must not be included in the DISTINCT count.
How do I do this please?
Thanks.
Solved! Go to Solution.
Hey,
I think this measure will do what you are looking for:
cnt Of IDs = var IDsToExclude = SELECTCOLUMNS( FILTER( ADDCOLUMNS( SUMMARIZE( 'Table1' ,Table1[ID] ) ,"MinValue", CALCULATE(MIN('Table1'[Value])) ) ,[MinValue] = 0 ) ,"ID", 'Table1'[ID] ) return COUNTROWS( EXCEPT( VALUES(Table1[ID]) ,IDsToExclude ) )
the variable IDsToExclude contains a table with a single column of the unique IDs.
Then EXCEPT purges the IDs from the list of all IDs, the remaining rows are simlpy counted.
Hopefully this is what you are looking for.
Regards,
Tom
Measure = COUNTROWS(filter(GROUPBY(Table1,Table1[id],"min_val",MINX(CURRENTGROUP(),Table1[value])),[min_val]=1))
it will display the count of uniwue rows
Hi,
This measure works as well
=COUNTROWS(FILTER(SUMMARIZE(VALUES(Table1[ID]),[ID],"ABCD",MIN(Table1[Value]),"EFGH",MAX(Table1[Value])),[ABCD]=[EFGH]))
Hope this helps.
Measure = COUNTROWS(filter(GROUPBY(Table1,Table1[id],"min_val",MINX(CURRENTGROUP(),Table1[value])),[min_val]=1))
it will display the count of uniwue rows
This works too. Good to know an alternative that I can use.
Of the two solutions I've been given - is one better than the other, and why?
Thanks.
Hey @Anonymous,
asking if one solution is better, this is difficult to answer.
From a performance point of view the solution of @balaganeshv2201 is faster (I tested it with datasets of 1, 10, and 100 million rows.
GROUPBY() has it's own subtleties you can read about it
here: https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/
here you will find something about using SUMMARIZE/ADDCOLUMNS as I did in my solution:https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
As always, it depends. Depending on the subtleties of your model and reporting requirements, I would stick with my solution. If your requirements are straightforward and the reporting requirements are simple (meaning no report and page level filters, go with the formula @balaganeshv2201 provided.
For a model of 100million rows (40million unique ID,~2.5 packages on average) using GROUPBY is 6times faster than SUMMARIZE/ADDCOLUMNS
Regards,
Tom
I did the performance comparison with DAX Studio available here:
https://www.sqlbi.com/tools/dax-studio/
Thank you!
You are welcome. If my reply helped, please mark it as Answer.
Hey,
I think this measure will do what you are looking for:
cnt Of IDs = var IDsToExclude = SELECTCOLUMNS( FILTER( ADDCOLUMNS( SUMMARIZE( 'Table1' ,Table1[ID] ) ,"MinValue", CALCULATE(MIN('Table1'[Value])) ) ,[MinValue] = 0 ) ,"ID", 'Table1'[ID] ) return COUNTROWS( EXCEPT( VALUES(Table1[ID]) ,IDsToExclude ) )
the variable IDsToExclude contains a table with a single column of the unique IDs.
Then EXCEPT purges the IDs from the list of all IDs, the remaining rows are simlpy counted.
Hopefully this is what you are looking for.
Regards,
Tom
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |