SUMX issue when having the same filter in the filters and in the table

[jaap_olsthoorn]

Hi all!

Sorry for the title, I just have no good way of explaining this issue. Example file at the bottom:

I have a table like this: 

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:

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

Then I filter on the owner babymarkt:

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:

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.

Does anyone have any clue as to what is happening here and how I should fix my measure?

Thanks! 

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

[Daryl-Lynch-Bzy]

@jaap_olsthoorn | @amitchandak - Adding this here as well based on this support ticket:

SUMX producing wrong result when filtered in both ... - Microsoft Power BI Community

test recovery =
SUMX (
    SUMMARIZE (
        'Test Table',
        'Test Table'[MLP],
        "Max", MAX ( 'Test Table'[Recovery] )
    ),
    [Max]
)

[v-xiaotang]

Hi @jaap_olsthoorn 

Thanks for reaching out to us.

if each MLP has 2 duplicate value, then you can try the 2 measures,

Measure 1 = MAXX(FILTER(ALL('Test Table'),'Test Table'[MLP]=MIN('Test Table'[MLP]) && 'Test Table'[Owner]=MIN('Test Table'[Owner])),[Recovery]) 

Measure 2 = IF(ISFILTERED('Test Table'[MLP]),[Measure 1],SUMX('Test Table',[Measure 1])/2) 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[jaap_olsthoorn]

No unfortunately that is not an assumption I can make there can be many of the same ones. Although I love the fact that people are trying to help me fix my measure, that's not really what I'm after. I think I found a bug in DAX, and would like to either get a confirmation that this is indeed a bug or an explanation of why it is not a bug.

[jaap_olsthoorn]

Anyone else have any idea why this is happening? Is there a bug in SUMX?

[amitchandak]

@jaap_olsthoorn , Try a measure like

test recovery =
SUMX(
Summarize('Test Table'[MLP],,'Test Table'[owner] ,'Test Table'[Recovery]), [Recovery])

[jaap_olsthoorn]

Thanks! That defo works in this example. I'm a bit worried about applyying this to my data model containing many columns though. But I can try!

Do you happen to know why the original measure does not work? I'd like to understand what I'm doing wrong.