Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |