Different results using variable instead of measure

[Anonymous]

Hello everyone!
Could someone pleae explain to me what's going on behind the scenes? I really can't understand what's happening.
So I have the visual shown below. I am only interested in the Before and After columns. The first screenshot is the correct one and the second is the problematic one. You see that in the second screenshot values on both columns are the same.

Now below I share all the measures I used for both columns. I also show what line I change to get from the correct one to the wrong one (it's on the last measure at the bottom):

Before Snapshot ID = 
CALCULATE(
    MIN('Snapshot Info'[Snapshot ID]); 
    FILTER(
        ALL('Snapshot Info');
        'Snapshot Info'[Date Time] = MIN('Snapshot Info'[Date Time])
    )
)

Before Avg Time Profile = 
CALCULATE (
    AVERAGE ( 'TIME PROFILE'[Value] );
    FILTER(
        'TIME PROFILE';
        'TIME PROFILE'[Snapshot ID] = [Before Snapshot ID]
    );
    TREATAS (
        VALUES ( 'Snapshot Info'[Start Time] );
        'TIME PROFILE'[Start Time Interval]
    )
)

After Snapshot ID = 
CALCULATE(
    MAX('Snapshot Info'[Snapshot ID]); 
    FILTER(
        ALL('Snapshot Info');
        'Snapshot Info'[Date Time] = MAX('Snapshot Info'[Date Time])
    )
)

After Avg Time Profile = 
var __After = 
    CALCULATE(
        MAX('Snapshot Info'[Snapshot ID]);
        FILTER(
            ALL('Snapshot Info');
            'Snapshot Info'[Date Time] = MAX('Snapshot Info'[Date Time])
        )
    )
return            
CALCULATE (
    AVERAGE ( 'TIME PROFILE'[Value] );
    FILTER(
        'TIME PROFILE';
        //This works (correct screenshot)
        'TIME PROFILE'[Snapshot ID] = __After
        //This doesn't (problematic screenshot)
        'TIME PROFILE'[Snapshot ID] = [After Snapshot ID]  
    );
    TREATAS (
        VALUES ( 'Snapshot Info'[Start Time] );
        'TIME PROFILE'[Start Time Interval]
    )
)

So could someone please explain to me what's happening? Thank you

[v-xicai]

Hi @Anonymous ,

In your scenario, there are two filter contexts  " FILTER( 'TIME PROFILE'…"  and " TREATAS( …)", and when we create the variable as "var __After =  CALCULATE(…) ", the expression runs on the two filter contexts , which is the Attribute in which the calculation is evaluated. As a result. for each Attribute , the AVERAGE ( 'TIME PROFILE'[Value] ) calculate within the whole scoop  filter contexts  " FILTER( 'TIME PROFILE'…"  and " TREATAS( …)".  So it will return correct result.

While for the separate measure [After Snapshot ID ],  values are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression of [After Avg Time Profile], which is the filter context.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai 

Thank you for your reply! Unfortunately I still couldn't understand what is happening. You said:

---

While for the separate measure [After Snapshot ID ],  values are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression of [After Avg Time Profile], which is the filter context.

---

So if I understand this correctly, [After Snapshot ID] has been calculated beforehand and its result is being used to filter [After Avg Time Profile]. But doesn't that mean that since I have the ALL(...) clause, I would always get the correct result and be able to filter out the values I need?

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may refer to the links:

https://community.powerbi.com/t5/Desktop/var-different-result/td-p/283143 ,

https://community.powerbi.com/t5/Desktop/VAR-different-result-than-without-it/td-p/518723,

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.