cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Using summarize gives wrong value

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.

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: Using summarize gives wrong value

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.

View solution in original post

Anonymous
Not applicable

Re: Using summarize gives wrong value

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors