cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bogomda
Helper II
Helper II

measure calculation - very odd problem

i wrote a simple measure

Managed Services =
CALCULATE(
SUM(Budget[Amount],
Budget[Account Group] = "Managed Services")

which internally should translate into:

 

 CALCULATE(
         SUM(Budget[Amount]),
         FILTER(
              ALL(Budget[Account Group]),
              Budget[Account Group] = "Managed Services"
  )

Measure returns incorrect result when i put Budget[Account Group] field as a row filter on the visual. i don't have any other filters on the report. What could be a problem?

 

Capture.PNG

1 ACCEPTED SOLUTION
Baskar
Resident Rockstar
Resident Rockstar

@bogomda 

 

Got it the issue, You are 100 % correct it is becasue of the sorting order. 

Try this, It will help you. 

 

Managed Services Budget ALL =
CALCULATE([Total Budget],FILTER(ALL(Budget[Account Group],Budget[Account Group Sort Order]),
Budget[Account Group]="Managed Services"))
 

View solution in original post

7 REPLIES 7
Baskar
Resident Rockstar
Resident Rockstar

@bogomda 

As per your dax code it is expected result. 

 

Let me know what is your expected output here ?

 

Do you want the same value in all blank cells ?

 

Try this 

 

Managed Services = CALCULATE(SUM(Budget[Amount]),
FILTER(ALL(Budget[Account Group]),Budget[Account Group]="Managed Services"))

Baskar, yes i need same value in all blank cells.

Measure below returns same result as the original measure. My understanding that internally they are identical anyway.

 

CALCULATE(

      SUM(Budget[Amount]),

      FILTER(

           ALL(Budget[Account Group]),

           Budget[Account Group]="Managed Services"

      )

)

 

i never seen such a problem before.

Capture.PNG

Baskar
Resident Rockstar
Resident Rockstar

@bogomda

 

Can you please share some sample data.

Baskar, I investigated a little bit further and found the couse of the problem which will be imporssible to identify without DAX studio.

 

The trace shows that when "sort by column" feature is used (in my case Budget[Account Group] is sorted by Column Budget[Account Group Sort Order]) and when sorted column is placed on visual, Power BI actually "places" both columns on visual 1) "sorted column" as well as 2) "sort by column". "Sort By Column" is in the visual for sorting purposes but somehow is hidden. Besides sorting, "Sort by column" also creates a side effect - add filter context which I don't see on visual but see in DAX query trace - as a result my measure gets filtered not by Budget[Account Group] but by Budget[Account Group Sort Order] column.

Knowing this behaviour of "sort by column", I was able to fix my measure by additing additional argument to CALCULATE (see in bold).

 

Thanks for your time in looking into it.

 

Managed Services Budget =
CALCULATE([Total Budget], Budget[Account Group] = "Managed Services",all(Budget[Account Group Sort Order]))

Capture.PNG

 

Baskar
Resident Rockstar
Resident Rockstar

@bogomda 

 

Got it the issue, You are 100 % correct it is becasue of the sorting order. 

Try this, It will help you. 

 

Managed Services Budget ALL =
CALCULATE([Total Budget],FILTER(ALL(Budget[Account Group],Budget[Account Group Sort Order]),
Budget[Account Group]="Managed Services"))
 

Baskar, here is the link to pbix file - OneDrive Link

 

https://1drv.ms/u/s!AhUWZ84uo7UAglYHcgbQ_CtW5OMq

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors