Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I've been slowly learning to use variables however I've run into a result that I dont understand
I have a table with Sales by Manager that is filtered by TopN 10 Managers based on sales.
I have the following measures, one just a sales total, another is two display a total that accounts for all sales not just top 10 managers.
Sum of sales = SUM ( 'FactSales'[Sales] ) Sales Top 10 = IF ( HASONEVALUE ( 'FactSales'[Manager] ); 'FactSales'[Sum of sales]; CALCULATE ( 'FactSales'[Sum of sales]; ALL ( 'FactSales'[Manager] ) ) )
This measure displays the correct total for all sales
I tried writting the same measure with variables
Sales Top 10 = VAR Sales = SUM ( 'FactSales'[Sales] ) RETURN IF ( HASONEVALUE ( 'FactSales'[Manager] ); Sales; CALCULATE ( Sales; ALL ( 'FactSales'[Manager] ) ) )
However it only shows the total for top 10.
Whats causing this behaviour?
Solved! Go to Solution.
Hi @Credo
When a variable is defined with VAR, it is evaluated in the context where it is defined, then it is effectively a constant when used in any subsequent expressions. Currently there is no way to define a measure locally within another measure, so I would recommend sticking with Sum of Sales as a separate measure and referencing that within Sales Top 10.
To explain further, in your second example, after the variable Sales is defined as SUM ( 'FactSales'[Sales] ), the expression CALCULATE ( Sales; ALL ( 'FactSales'[Manager] ) ) gives exactly the same result as Sales, since Sales is effectively a constant, and modifying filters makes no difference to a constant.
Regards,
Owen
Hi @Credo
When a variable is defined with VAR, it is evaluated in the context where it is defined, then it is effectively a constant when used in any subsequent expressions. Currently there is no way to define a measure locally within another measure, so I would recommend sticking with Sum of Sales as a separate measure and referencing that within Sales Top 10.
To explain further, in your second example, after the variable Sales is defined as SUM ( 'FactSales'[Sales] ), the expression CALCULATE ( Sales; ALL ( 'FactSales'[Manager] ) ) gives exactly the same result as Sales, since Sales is effectively a constant, and modifying filters makes no difference to a constant.
Regards,
Owen
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
115 | |
74 | |
57 | |
47 | |
38 |
User | Count |
---|---|
167 | |
117 | |
61 | |
58 | |
46 |