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.
Hi everyone,
I am having some issues with the Summarize function as it is not giving the correct value for a specific measure. I'll explain below.
I have a sample HR table where a person has one row/record per month.
I have the following calculation:
MyCalculation = COUNTX( FILTER( SUMMARIZE('Sample data';'Sample data'[userID]; "Leave";SUM('Sample data'[leaveDays]); "UnitChange";CALCULATE(DISTINCTCOUNT('Sample data'[Unit]);ALL('Sample data'[Unit]))); [Leave]<=2 && [UnitChange]<2); [Leave])
The bolded part above is supposed to count how many Units a userID has belonged to(as per the summarize function).
However, upon inspection all userIDs get assigned value 1 on UnitChange(1 is the least they can have) even in cases where they actually have a higher number.
So Summarize does not seem to calculate the correct value for the UnitChange measure..
I've been stuck on this for a while now.. Anyone have an idea?
Thank you
Solved! Go to Solution.
Hi @Anonymous
The below article explains how Summarize works, the common practice is to use summarize for group by and then ADDCOLUMN() for Aggregations.
https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
The below article explains how Summarize works, the common practice is to use summarize for group by and then ADDCOLUMN() for Aggregations.
https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the article!
It makes perfect sense now.
Although it seems Summarizedcolumns seems to be amore efficient way to use this.
Will look into it
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |