Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@Anonymous
In that case
= SUMX ( CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ), [Amount] )
@Anonymous
Try
Column = SUMX ( VALUES ( [AMOUNT] ), [Amount] )
Thank you Zubair. But it give me sum of including all other ids. I need sum of Distinct values per distinct id.
For example
ID Amount Sum
1 10 30
1 20 30
1 20 30
1 10 30
2 5 5
2 5 5
2 5 5
3 10 45
3 5 45
3 10 45
3 30 45
3 5 45
@Anonymous
In that case
= SUMX ( CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ), [Amount] )
@LivioLanzo Would you please help me understand this dax - how it is performing the calculation?
= SUMX ( CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ), [Amount] )
Hi @Anonymous
I am so sorry. I missed your notification earlier
This is how it works. Following table creates a Single Column Table Containing distinct Values (for each ID)
CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] )
Then we use an ITERATOR function (X function) to sum the values of this table
So
SUMX(previoustable,ColumntoSum)
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!
@Anonymous
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
thanks so much - this explanation really helped. Using CALCULATETABLE to reduce the table to just one row per duplicate ID is great!
@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.
@Anonymous
I will get back to you.
@Zubair_Muhammad Thank you so much Sir! It seems to be working :). Would you please help me understand this formula?
Appreciate your kind help!
Thank you again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |