Context transition with variable

[mgrayTCB]

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.

[SpartaBI · Accepted solution]

@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 · Accepted solution]

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

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

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

[SpartaBI]

@mgrayTCB yep, I actually will write something I think is a better version from performance view. One minute.

P.S.
Check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂

[SpartaBI · Accepted solution]

@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

      

[mgrayTCB]

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!

[SpartaBI]

@mgrayTCB exactly! To distinguish between model columns and temporary query columns inside a measure.

Please go check my report and kudos it of you like it 😃😃

[mgrayTCB]

hey - your two measures are returning different results and I cant figure out why.  MS Delay Faster is the correct answer (average of the delays not = 0.

[SpartaBI]

@mgrayTCB Hey, first of all in your screenshot it seems you did a major change.
In one you use VALUS('Project'[ProjectName]) and in the other you use the table 'Project'. 
Please check both of them with the same expression and let me know (2 x 2 checks).
P.S. check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[mgrayTCB]

I adjusted them both to use values(project[projectname]) and I still get different answers. The MS Delay Faster does produce the correct averge of the items above but I dont understand why MS Delay produces a different answer. It is always different even with different MS Short Names selected. It is always lower.

[SpartaBI]

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

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]

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

[SpartaBI]

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

[mgrayTCB]

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

[SpartaBI · Accepted solution]

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

That did it! Thank very much!

[SpartaBI]

@mgrayTCB my pleasure 🙂

[mgrayTCB]

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 · Accepted solution]

@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

[Whitewater100]

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