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 the average delay across mulitple selected projects. This two step method works:

 

Measure 1

MS Months Chg =
var SSdate = MINX(MilestoneSS,MilestoneSS[DateActOrEstSS])
var Curdate = Maxx(Milestones,Milestones[DateActOrEst])
return
DATEDIFF(SSdate,Curdate,day)/30.4
 
Measure 2 - this limits the list to only projects with changes <> 0 and produces the correct average
MS Delay =
CALCULATE(
AVERAGEX(
FILTER(values(Project[ProjectName]),[MS Months Chg]<>0),
[MS Months Chg]))
 
I then tried to consolidate this into one measure and I no longer get the correct average but I dont understand why.
 
MS Months Chg2 =
var SSdate = MINX(MilestoneSS,MilestoneSS[DateActOrEstSS])
var Curdate = maxx(Milestones,Milestones[DateActOrEst])
var datechg = DATEDIFF(SSdate,Curdate,day)/30.4
return
CALCULATE(
AVERAGEX(
FILTER(
Project,
datechg <> 0),
datechg
)
)
 
I must be missing something silly.
 
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. 

31 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    I beleive it's due to having multiple iterators with calculate. 

     

    Would somehing like this work?

     

    CombinedMeasure = IF([MS Months Chg]<>0, [MS Months Change], BLANK())

    • mgrayTCB's avatar
      mgrayTCB
      Helper 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
        Solution Sage
        4 years ago

        Hi:

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

  • SpartaBI's avatar
    SpartaBI
    Community 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
)

     

     

     


    Showcase Report – Contoso By SpartaBI


          

    • mgrayTCB's avatar
      mgrayTCB
      Helper IV
      4 years ago

      Thank you that makes sense regarding the scalars getting fixed and I understand the logic of your revised measure but the last reference to the "datechg" as the expression term of the AverageX seems to be out of scope or something. See below.

       


       

       

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        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