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]
)

     




    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

    Showcase Report – Contoso By SpartaBI


    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    SpartaBI Logo

  • 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. 

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

mgrayTCB 
The var for scalar values are fixed after their execution in the original filter context. 
try this:

 

 

 

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

 

 

 


In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

Showcase Report – Contoso By SpartaBI


Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

SpartaBI Logo

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

Thank you. I see you need to re do it all.

    • SpartaBI's avatar
      SpartaBI
      Icon for Community Champion rankCommunity Champion
      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]
)

       




      In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

      Showcase Report – Contoso By SpartaBI


      Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

      SpartaBI Logo

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

        Thank you so much! I was wondering about performance and before I could even test it in my model you rewrote it. I have never seen the "@" syntax. Is that just your convention for columns of virutal tables?

        thanks again!