Forum Discussion
Sum of Distinct ID with multiple Distinct values
Anonymous
In that case
= SUMX ( CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ), [Amount] )
Thank you so much Zubair_Muhammad .
So ALLEXCEPT ( Table1, Table1[ID] ) tells the formula to return distinct values for each ID. If I use any other column e.g., Type in place of ID then the overall calculation will bring distinct values for each Type? Am i understanding it correctly?
Thank you once again for all your time and help!
Community ChampionAnonymous
Allexcept returns a table with all filters removed except for the filters on the specified columns.
So for each ID (each row) you get a filtered table containing all rows with the same ID.
Then CALCULATETABLE takes the distinct values from that Filtered Table
Here are some good REFERENCES for allexCEPT
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
https://docs.microsoft.com/en-us/dax/allexcept-function-dax
Zubair_Muhammad Thank you so so much!
What is the easiest way to see the actual data of each row that is beaing calculated by formula:
CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] )
I mean, is there any way I can practiacally see the - filtered table containing all rows with the same ID for each ID (each row)?
I understand the logic, but I want to play with different combinations so want to see the actual data that is being returned by this formula in my data model.
Kindly advice! Thank you again for all your time and knowledge sharing.
Zubair_Muhammad Please help me with above qquestion. I think what I am trying to ask, how can I debug nmy dax when it returns a table of data rather than scaler value for particular row item. Please read my above post as well.
Thank you for all kind help.
- Zubair_Muhammad
Anonymous
I will get back to you.
thanks so much - this explanation really helped. Using CALCULATETABLE to reduce the table to just one row per duplicate ID is great!
