Fixed value upon merging two measures while using first as variable in second.

[iamprajot]

Hi

I am using 2 measures as given below and want to merge them into one but upon merging (using calculations of first in second) these 2, the answer is wrong and a fixed value.

Maybe if using the Source calculation as variable in the Sales causes the value to become fixed - how can that be dynamic ?

Sales Source =

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] )

RETURN

SWITCH ( SELECTEDVALUE(_Currency[Currency]), "CZK", CZK, "USD", USD, "EUR", EUR )

Sales =

VAR _Start = DATE(2012,1,1)

VAR _End = DATE(2020,12,31)

RETURN

CALCULATE([Sales Source],B06_CO_with_Deliveries_and_Sales_Accruals[ACCRUALS_IN_DIVISION_100_FLAG] IN {"Yes","No"})

Target = 

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] )

VAR Sales_Source = 
SWITCH ( SELECTEDVALUE(_Currency[Currency]), "CZK", CZK, "USD", USD, "EUR", EUR )

VAR _Start = DATE(2012,1,1)

VAR _End = DATE(2020,12,31)

RETURN

CALCULATE([Sales_Source],B06_CO_with_Deliveries_and_Sales_Accruals[ACCRUALS_IN_DIVISION_100_FLAG] IN {"Yes","No"})

Thanks

[AlB · Accepted solution]

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 

[AlB · Accepted solution]

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 

[AllisonKennedy]

@iamprajot  Do you get the correct result when they are separate? Why do you need to merge them into one measure? See if this post helps explain how measures have an implicit CALCULATE around them which causes a context transition: DAX Context Transition: Why it can be handy to use a [Measure] inside a Measure 

[iamprajot]

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.

[AllisonKennedy · Accepted solution]

@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)

RETURN

CALCULATE(

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] )

RETURN

SWITCH ( SELECTEDVALUE(_Currency[Currency]), "CZK", CZK, "USD", USD, "EUR", EUR )

,B06_CO_with_Deliveries_and_Sales_Accruals[ACCRUALS_IN_DIVISION_100_FLAG] IN {"Yes","No"})