Forum Discussion
Fixed value upon merging two measures while using first as variable in second.
- 5 years ago
Hi iamprajot
Variables in DAX are immutable. Their value will not change after declaration so applying a CALCULATE on them will not have any effect whatsoever. You are also referencing a variable between brackets [ ]. That should generate an error. Try this:
Target = VAR _Start = DATE ( 2012, 1, 1 ) VAR _End = DATE ( 2020, 12, 31 ) RETURN CALCULATE ( SWITCH ( SELECTEDVALUE ( _Currency[Currency] ), "CZK", SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_CZK] ), "USD", SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_USD] ), "EUR", SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_EUR] ) ), B06_CO_with_Deliveries_and_Sales_Accruals[ACCRUALS_IN_DIVISION_100_FLAG] IN { "Yes", "No" } )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- 5 years ago
iamprajot Variables are calculated when they are defined, which is the beauty of them. It allows us to create static values, grand totals, or even row context calculations, etc and then use that number later in the calculation.
So, wherever you first define the variable, DAX will calculate its value, and replace all the formlua with that value for any time you use that variable. It does NOT replace with the formula.
Measures are different. They are always calculated on demand, but they have an implicit CALCULATE function around them. So, whenever you use a measure in a DAX formula, DAX replaces that measure with CALCULATE(measure formula). It does not replace with the value like variables.
In many cases, using a variable to define your measure and combine into one won't change the final result, but in your case you need to keep the context that was happening when you were using the measure, so please use the results AlB has suggested.
As a messy starting point, you can simply replace your measure with the formula IN THE SAME LOCATION (not using variables), and then simplify from there to get what AlB has suggested. Here is the initial (messy) replacement:
Sales =VAR _Start = DATE(2012,1,1)VAR _End = DATE(2020,12,31)RETURNCALCULATE(VAR CZK =SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_CZK] )VAR USD =SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_USD] )VAR EUR =SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_EUR] )RETURNSWITCH ( SELECTEDVALUE(_Currency[Currency]), "CZK", CZK, "USD", USD, "EUR", EUR ),B06_CO_with_Deliveries_and_Sales_Accruals[ACCRUALS_IN_DIVISION_100_FLAG] IN {"Yes","No"})
Hi iamprajot
Variables in DAX are immutable. Their value will not change after declaration so applying a CALCULATE on them will not have any effect whatsoever. You are also referencing a variable between brackets [ ]. That should generate an error. Try this:
Target =
VAR _Start =
DATE ( 2012, 1, 1 )
VAR _End =
DATE ( 2020, 12, 31 )
RETURN
CALCULATE (
SWITCH (
SELECTEDVALUE ( _Currency[Currency] ),
"CZK", SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_CZK] ),
"USD", SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_USD] ),
"EUR", SUM ( B06_CO_with_Deliveries_and_Sales_Accruals[ACTUAL_AMOUNT_IN_EUR] )
),
B06_CO_with_Deliveries_and_Sales_Accruals[ACCRUALS_IN_DIVISION_100_FLAG]
IN { "Yes", "No" }
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers