Forum Discussion
Fixed value upon merging two measures while using first as variable in second.
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
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"})
Thanks for replying AllisonKennedy and yes I am getting the correct result as there is not much happening in the DAX code.
However what I am trying to do is if everything can be done in a single measure with the use of variables then why use multiple measures referencing dependent measures. It is easy that way to manage if everything is in one place rather then finding dependencies.
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: