Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

SUMX producing wrong result when filtered in both row context and filter context

Hi all!

 

Sorry for the title, I just have no good way of explaining this issue. Example file: https://drive.google.com/file/d/1lc-GdEEYAMYjqrm1YfVm9wuWE9Ls_gRq/view?usp=sharing

 

I have a table like this: 

jaap_olsthoorn_0-1663061245948.png

LP is the lowest granularity. MLP is the "master" LP. So 2 LP's can be part of 1 MLP.
Recovery is a value that is stored on each row of this table, but when summing it, you are only supposed to take it once for each value of MLP, not for each value of LP.
Finally, I create a random filter, something that splits the rows. I dont really care about this filter, but I need it to trigger my issue.

 

I created this measure: 

test recovery =
SUMX(
    VALUES('Test Table'[MLP]),
    CALCULATE(MAX('Test Table'[Recovery]))
)

The idea being using MLP as the iterator while calulating the max of recovery for each MLP, and then summing those amounts.

I then create the following table, with these filters:
jaap_olsthoorn_1-1663061514142.png

The result is exactly as expected. The total works, the values work, great.

 

Then I filter on the owner babymarkt:

 

jaap_olsthoorn_2-1663061635192.png

 

Still perfect results. 

 

Now I'm going to filter on my random filter that should, as far as I understand it, have no effect, because all rows of babymarkt have filter value 1:

 

jaap_olsthoorn_3-1663061700707.png

The totals rows are still correct, but the individual MLP rows are no longer, and now show the totals too.
When I remove the filter on Owner, the results are as expected again.

 

Something seems to be happening with the owner filter. When I filter for owner only through the row context, it works fine, but when I filter through both the row and filter context, it messes up somehow once I add any other filter.

 

I am not interested in solving this issue (I have received some good suggestions already), but I am interested in understanding why SUMX messes up in this situation, and if, possibly, I have found a bug in DAX that I can use to impress Marco & Alberto with 😉

Thanks! 

https://drive.google.com/file/d/1lc-GdEEYAMYjqrm1YfVm9wuWE9Ls_gRq/view?usp=sharing

Status: Investigating

 

SUMX is an iteration function in Power BI that works on a row-by-row calculation per the given expression or equation. This function considers each row at a time and applies the calculation. It will not concentrate on the entire column, unlike the SUM function. But, it works like a cell be cell formula in Excel. SUM is an aggregate function, and SUMX is an expression function. One can perform Power BI data manipulation by using DAX functions, and SUMX is one such function in Power BI. 

 

The SUMX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.

  • Only the numbers in the column are counted. Blanks, logical values, and text are ignored.

  • For more complex examples of SUMX in formulas, see ALL and CALCULATETABLE.

  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

 

Comments
jaap_olsthoorn
Advocate II

Hey William,

 

Yeah good point. It works in a star schema and that is defo the best solution!

 

But as a former software tester, that answer is just not good enough, hahahah.