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

      

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

[mgrayTCB]

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]

Hi:

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

[mgrayTCB]

See the sample data here

https://drive.google.com/file/d/1F-60fTrgFjDy53ycBb7uqlwDEjcNleP1/view?usp=sharing

Why does the two step measure approch provide the correct average while the AllInOneMeasure does not.

[Whitewater100]

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]

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

[Whitewater100]

Hi MGray:

Sorry it's taken a while. It's a tricky one and I have a question. I will paste two examples of filtered data to ask you what is the correct answer you desire? I will also attach the file as I added a date table and have a few measures in "New Calculations" table.

Here are a couple of possible discrepancies: What should the answer be?

Once we know how to handle these, I think we can arrive at a solution.

Thanks..https://drive.google.com/file/d/13V1MW5t98F67ygXf3_rFEYmq8S2uW-Id/view?usp=sharing 

[mgrayTCB]

Thanks for diggin into this. I am not following what your question is. The measure DelayStep2 provides the correct answer (average project milestone delay) but relys on a intrim measure DelayStep1. All I am trying to do is get the same answer but all in one measure. When I do it all in one measure how do I get datechange to calculate row by row? 

[Whitewater100]

Good am:

My question are that the two examples show the data from the table flitered down to two specific locations. I am trying to ask what answer do you want? I'll explain example one below. Proj New 125 Am only has one entry. Over a two month period the delay is 65 days. The average delay measure shows 2.14.(no filters). Is 2.14 correct? 65/30.4. I was thinging a 65 day delay over approx two months could result in a result of 65/2 = 32.5?

As we look at(I pasted yesterday example #2). TCB Park H. It has 5 entries. Only one have info (28 days over about one month). Your measure results in 6.51 and mine is .92. 

Basically, Im just checking in on the math you want to use for the calculation. Right now I'm using a monthly average.

I hope these questions make sense.

Thank you..

[mgrayTCB]

Thank you for explaining. In both of those filtered cases the answer would be the one number that shows.

So for TCB Park H we would not average in all the zeros (they would be filtered out of the result). That is what the AverageX of the filtered table does in my working measure.

The average I am looking for is the average of those milestones that have delay <> 0

[Whitewater100]

Hi:

I'm having trouble trying to match your figure. https://drive.google.com/file/d/13V1MW5t98F67ygXf3_rFEYmq8S2uW-Id/view?usp=sharing 

I'm hoping one of the experts on here can help? The file attached has some new overview calculations that could save some time figuring this out.

Sorry I couldn't match or misunderstanding...

[mgrayTCB]

Maybe I will repost with a differnt subject. Thanks for trying

[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 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

[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 🙂