Forum Discussion

mgrayTCB's avatar
mgrayTCB
Icon for Helper IV rankHelper IV
4 years ago
Solved

Context transition with variable

when i calculate this average with two separate measures it works correctly but when I try to combine it into one meaure with an extra variable it stops working correctly. I am trying to calculate th...
DAX Calculation
Variable calculation
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    mgrayTCB hey, yes, I actually fixed that 20 minutes ago in the original message but seems I didn't click post haha 🙂 One sec, will update the original code

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    mgrayTCB try this:

     

    MS Months Chg2 =
AVERAGEX (
    FILTER (
        ADDCOLUMNS (
            Project,
            "@datechg",
                VAR SSdate =
                    CALCULATE ( MINX ( MilestoneSS, MilestoneSS[DateActOrEstSS] ) )
                VAR Curdate =
                    CALCULATE ( MAXX ( Milestones, Milestones[DateActOrEst] ) )
                VAR datechg =
                    DATEDIFF ( SSdate, Curdate, DAY ) / 30.4
                RETURN
                    datechg
        ),
        [@datechg] <> 0
    ),
    [@datechg]
)

     




    Showcase Report – Contoso By SpartaBI


          

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    mgrayTCB no problem.

    Please also add calculate on top of minx and maxx in lines 7 and 9 and you can actually remove the calculate we added before, it has no meaning. All there is vars anyway.

    BTW, in general, you don't need the calculate in line 2. 

mgrayTCB's avatar
mgrayTCB
Icon for Helper IV rankHelper IV
4 years ago

the combined measure that is not working is the one with the variable datechg. I tried that if statement there but it does not help. Any other ideas?

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

If you have sample data it will be easier to try to solve. Thank you.

    • Whitewater100's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi:

      I did not see Milestones field? I have updated a few things but not traking on the measures you mentioned regarding Milestones.

      https://drive.google.com/file/d/1PhhAyK-UJO7xWJocDYgh3VhzM4X-_hM9/view?usp=sharing 

       

      Both FILTER & the X functions are iterators. When used together with calculate you can get unexpected results. The second iterator has another row context vs first iterator. 

       

      Note - I know you are looking for average differences. Is this for just for months with data or by month even though there is no data?

       

      Thanks..

      • mgrayTCB's avatar
        mgrayTCB
        Icon for Helper IV rankHelper IV
        4 years ago

        I think I may have shared the wrong file. Try this one.

        https://drive.google.com/file/d/1MePchJEuDLB1UF6gDvrvX88Rx9WbKdWS/view?usp=sharing

        The milestone field is called Mstype but should not matter for this calculation.

        I am try to calculate the average delay all in one measure but can only get the write answer if I do it in two steps.


         

        5.75 is the right answer - it is the averagex of the displayed projects.  24.47 is not the average of each it is the delay between the earlist date (DateActOrEstSS) and the max of the the latest (DateActOrEst). 

         

        I just dont understand what I am doing wrong in the DelayAllInOneMeasure and not able to get 5.75