Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgrayTCB
Helper III
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

@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

View solution in original post

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

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

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

View solution in original post

31 REPLIES 31
SpartaBI
Community Champion
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
)

 

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

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

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

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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!

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

mgrayTCB_0-1653599503593.png

mgrayTCB_1-1653599580362.png

 

mgrayTCB_2-1653599625220.png

 

@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_0-1653653332553.png

mgrayTCB_1-1653653380446.png

mgrayTCB_2-1653653431658.png

 

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

mgrayTCB_0-1653657096468.png

 

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

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

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

mgrayTCB_0-1653661006833.png

 

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

That did it! Thank very much!

@mgrayTCB my pleasure 🙂

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.

mgrayTCB_1-1653592561456.png

 


 

 

@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
Solution Sage
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())

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors