Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Using summarize gives wrong value

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

2 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion

    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's avatar
      Anonymous
      Not applicable

      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