Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
When I enter part of a sumx expression as variable instead of directly coding it in the measure, I get a different (the expected) result.
Below a simplified example. Sample data =
Month | AccountID | BalanceTotal |
1 | 1001 | 500 |
1 | 1002 | 500 |
2 | 1001 | 500 |
2 | 1002 | 500 |
Expected result of measure
Multiply the balance of each row with the max selected month. So when both months selected, multiply each 500 with 2.
Note: I know in this simplified example I don't need sumx but can simply do sum(Balance)*calculate(max(allselected,Month)). But in the more complex real life example I do need a sumx measure and the difference is same as below.
I originally created this measure 'Balance x maxmonth':
var _sumx =
sumx(Balances,Balances[Balance]*
calculate(
max(Balances[Month])
,ALLSELECTED(Balances[Month])
)
)
return
_sumx
This does not return the expected result when a visual is aggregated on month level. However if I replace the 'calculate' part of the sumx expression into a variable like below it does:
Balance x maxmonth with var =
var _maxmonth =
calculate(
max(Balances[Month])
,ALLSELECTED(Balances[Month])
)
var _sumx =
sumx(Balances,Balances[Balance]*
_maxmonth
)
return
_sumx
Results with and without variable for the calculate part of the expression:
Can anyone explain why the use of a var could lead to a different result outcome?
I'm happy with the fix but want to understand this difference in behavour when using variables within a sumx expression.
Many thanks.
Solved! Go to Solution.
@akapelle In the first case, the CALCULATE is being evaluated for each row while in the second case it is evalulated once within the full context of the measure.
@akapelle In the first case, the CALCULATE is being evaluated for each row while in the second case it is evalulated once within the full context of the measure.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |