Can't solve context transition in DAX

[JD_]

Hello, 

First, I would like to thank you for taking the time to read me and to help me, i'm strugggling with this issue. 

I'm migrating a report from Tableau to PowerBI for my work and I have somes discrepancies between both. 

In fact, I obtain the same values when the calculations are in a row level but it's not the case anymore when it's on an aggregated level (matrix table). It's the case for differents values but I suspect a specific measure at the root of others calculations. 

Looking on internet, it seems related to the context transition in DAX. I tried a lot of different things to fix this but nothing worked. So i'm afraid to not have enough knowledge in dax to solve it. That's why i'm asking your help. 

First thing to know is that the data model is pretty easy, it's a single table containing few columns we need to make calculations. 

Those calculations are nested so I have to talk about different measures to know which one is linked to the context transition issue.

I will take the easiest as example : 

I have to count serial numbers accross different dimensions ; the first is just a distinctcount and the second  a distinctcount with condition. 

1) Serial Number Distinctcount = DISTINCTCOUNT('table'[SERIAL_NUMBER])

2)  Complete Serial Numbers = IF(measure1 + measure2 + measure3 > 0, [Serial Number Distinctcount])

At a row level, it distinguishes all the serial number and the complete one. However, it's not the case at an aggregated level (in matrix table) where both measures returned identical values. It's the same for others calculations so I suspect one variable among the condition (MEASURE3) because it is used in each wrong calculation. 

The MEASURE3 measure is pretty big but basically wants to get the average of a measure for a specific scope/dimensions. The scope formula (removefilters/summarize) should replace the "Fixed" LOD in tableau. 

There is different level of granularity depending if the previous one is blank or not, I will only show the first one to be brief. 

MEASURE3 =

VAR granularity1=

CALCULATE(

    AVERAGEX('table', [Spend measure])

    , REMOVEFILTERS('table'),

    SUMMARIZE('table',

    [COLUMN1], [COLUMN2], [COLUMN3])

)

VAR granularity2/3/4/5 = same pattern with different columns in the summarize function. 

RETURN

SWITCH(

    TRUE(),

    NOT(ISBLANK(granularity1)), granularity1

    ,NOT(ISBLANK(granularity2)), granularity2

    ,NOT(ISBLANK(granularity3)), granularity3

    ,NOT(ISBLANK(granularity4)), granularity4

    , granularity5

)

This measure3 is used in different calculations so I think that it's this one that causes the issue. 

There is also a measure at the root of this one (spend measure) and so on, so it's maybe "deeper" but I will stop there to not overload you. I will add the details if needed but in sum, the measures are mainly based on an iterator function to retrieve something and apply more calculations or filters. 

I read about context transition so I tried to use CALCULATE() before the iterators and/or use measures in the calculation but nothing worked. 

Hoping you could help me !

Many thanks in advance, 

[JD_ · Accepted solution]

I found the issue. 
It was related with a deeper variable, where Variables were used outside the iterator. 

Thanks 

[amitchandak]

@JD_ ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I have discussed how things work differently here

Tableau Vs Power BI: https://www.youtube.com/watch?v=tAmg00Wf_cw&list=PLPaNVDMhUXGYzjFASXjdY7GNoFxvlkR54

[JD_]

Hello, 

Thank you for answering. 

Here some outputs

The matrix table in PBI doesn't get different values between measures (Serial number/Complete SN ; total 1/total2) 

However, it's the case in tableau (it's a new data source updated in PBI so it's normal if the total is not exactly the same between both, but the ratio and the difference should be there)

However, at a row level I obtain same results in PBI and Tableau, with a difference between the previous measures 

The difference between both totals is VAR3, that's why I think this is the issue. 
But once again, there are nested calculations so the root is maybe deeper 

Thanks in advance,