Using summarize gives wrong value

[Anonymous]

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

[Mariusz · Accepted 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.

[Anonymous]

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