Forum Discussion

JD_'s avatar
JD_
Frequent Visitor
3 years ago
Solved

Can't solve context transition in DAX

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, 
 
context
DAX
Help
transition
  • JD_'s avatar
    JD_
    3 years ago

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

    Thanks 

3 Replies

    • JD_'s avatar
      JD_
      Frequent Visitor
      3 years ago

      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,

  • JD_'s avatar
    JD_
    Frequent Visitor
    3 years ago

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

    Thanks