Forum Discussion
Microsoft EmployeeDISTINCT over SUMMARIZE
- I do not see the need for DISTINCT, SUMMARIZE already makes things distinct.
Maybe, but don't know your data to be sure. Should just be able to use COUNTROWS and FILTER the table returned from SUMMARIZE but not sure that is more efficient or not.
Community ChampionMaybe, but don't know your data to be sure. Should just be able to use COUNTROWS and FILTER the table returned from SUMMARIZE but not sure that is more efficient or not.
karun_rGreg_Deckler Yes, that's what I thought. I removed DISTINCT from the measure definition and the numbers seem to match. I am just trying to think of a case where I would need a DISTINCT over SUMMARIZE. It's impossible for me to test all possible combinations considering the dimensions I have in my dataset. In your experience, was there ever a case where you had to apply DISTINCT over a SUMMARIZE statement ? I am yet to do a performance comparison between both the measures.
- Greg_Deckler
No on DISTINCT with SUMMARIZE, because when you SUMMARIZE, whatever your are grouping on will be distinct. DISTINCT will make sure that entire rows are distinct in a table versus just a particular column, but the fact that you are using SUMMARIZE ensures that the table rows are distinct, it's built into the grouping.
Ashish_Mathur put into a formula what I was discussing with COUNTROWS and FILTER. Now, whether that is an improvment? You'd have to test it. See my 4 part series on Performance Tuning DAX: https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
- karun_r
Ashish_Mathur and Greg_Deckler So I had three copies of the measures for which I tested the performance on my Power BI desktop. SUMX() was consistently better performing than COUNTROWS(FILTER() >0) pattern
And removing DISTINCT from SUMMARIZE also had some minor improvements on the run time. Maybe it would change in a huge dataset and the time difference would be considerable.