Forum Discussion

mgrayTCB's avatar
mgrayTCB
Helper 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. 

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

mgrayTCB can you test this scenario and put it also in the visual:

 

MS Months Chg2 Wrong =
AVERAGEX (
        ADDCOLUMNS (
            VALUES('Project'[ProjectName]),
            "@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]
)

 



mgrayTCB's avatar
mgrayTCB
Helper IV
4 years ago

That seems to be averaging in zeros. You are no longer limiting the virtual table to datechg <> 0. Why is that not working in the version without the virtual column?

 

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    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
    Helper IV
    4 years ago

    That did it! Thank very much!

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    mgrayTCB exactly what I wanted to test 🙂
    I need to think about it 🙂

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    mgrayTCB in ms delay can you also add calculate on top of the date diff 4 rows before the end of the code

  • mgrayTCB's avatar
    mgrayTCB
    Helper IV
    4 years ago

    Thanks again for digging in to this. Unfortunatly that addtional calculate did not help

     

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    mgrayTCB my pleasure 🙂