cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

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

3 ACCEPTED SOLUTIONS
Community Champion

@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

Community Champion

@mgrayTCB try this:

``````MS Months Chg2 =
AVERAGEX (
FILTER (
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

Community Champion

@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 31
Community Champion

@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

Helper III

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

Community Champion

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

Community Champion

@mgrayTCB try this:

``````MS Months Chg2 =
AVERAGEX (
FILTER (
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

Helper III

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!

Community Champion

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

Helper III

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.

Community Champion

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

Helper III

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.

Community Champion

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

``````MS Months Chg2 Wrong =
AVERAGEX (
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]
)``````

Helper III

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?

Community Champion

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

Community Champion

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

Helper III

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

Community Champion

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

Helper III

That did it! Thank very much!

Community Champion

@mgrayTCB my pleasure 🙂

Helper III

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.

Community Champion

@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

Solution Sage

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors