Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iamprajot
Responsive Resident
Responsive Resident

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

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

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

View solution in original post

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

AllisonKennedy
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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: 

 

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"})

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.