Forum Discussion
Context transition with variable
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] )
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 😃😃
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.
That did it! Thank very much!
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 🙂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.
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] )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?
Thanks again for digging in to this. Unfortunatly that addtional calculate did not help