topic Re: Context transition with variable in DAX Commands and Tips

Context transition with variable

mgrayTCB — Tue, 24 May 2022 13:45:47 GMT

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.

Re: Context transition with variable

Whitewater100 — Tue, 24 May 2022 13:58:40 GMT

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

Re: Context transition with variable

mgrayTCB — Tue, 24 May 2022 15:35:46 GMT

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?

Re: Context transition with variable

Whitewater100 — Tue, 24 May 2022 17:07:20 GMT

Hi:

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

Re: Context transition with variable

mgrayTCB — Tue, 24 May 2022 19:15:38 GMT

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.

Re: Context transition with variable

Whitewater100 — Wed, 25 May 2022 11:35:02 GMT

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

Re: Context transition with variable

mgrayTCB — Wed, 25 May 2022 11:52:03 GMT

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

Re: Context transition with variable

Whitewater100 — Thu, 26 May 2022 00:59:12 GMT

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

Re: Context transition with variable

mgrayTCB — Thu, 26 May 2022 11:48:31 GMT

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?

Re: Context transition with variable

Whitewater100 — Thu, 26 May 2022 12:52:50 GMT

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

Re: Context transition with variable

mgrayTCB — Thu, 26 May 2022 15:16:04 GMT

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

Re: Context transition with variable

Whitewater100 — Thu, 26 May 2022 16:55:42 GMT

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

Re: Context transition with variable

mgrayTCB — Thu, 26 May 2022 18:04:35 GMT

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

Re: Context transition with variable

SpartaBI — Thu, 26 May 2022 19:29:56 GMT

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

Re: Context transition with variable

mgrayTCB — Thu, 26 May 2022 19:16:24 GMT

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.

Re: Context transition with variable

SpartaBI — Thu, 26 May 2022 19:29:15 GMT

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

Re: Context transition with variable

mgrayTCB — Thu, 26 May 2022 19:30:28 GMT

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

Re: Context transition with variable

SpartaBI — Thu, 26 May 2022 19:32:24 GMT

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 🙂

Re: Context transition with variable

SpartaBI — Thu, 26 May 2022 19:35:03 GMT

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

Re: Context transition with variable

mgrayTCB — Thu, 26 May 2022 19:46:15 GMT

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!