Variable not evaluating inside switch statement, individual calculation is working fine.

[Anonymous]

Hello When i combine Variables with Switch statement it doesn’t work. But instead of variables I write entire calculation it work perfectly.
Below are 2 scenario of my measure, The first scenario doesn’t work if i use variable, but second work perfectly if i don't use variables. (The Dark Grey Bar's are the one causing issue)
Can anyone please help what i am doing wrong in first scenario.
merry christmas

scenario 1 (Incorrect) :-

Total_workforce_FLC EURO(M/Y-1) =

Var FirstSUm = CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

Var SecondSum = CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

return

if(HASONEVALUE('Axis_Table'[Type]),

SWITCH(VALUES(Axis_Table[Type])

,"Year",CALCULATE(FirstSUm +

SecondSum

,TREATAS(VALUES('Axis_Table'[Values])

,'Calendar'[Year_Cal]))

,"Month",CALCULATE(FirstSUm +

SecondSum

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[MonthYear_Cal]))

,"Quarter",CALCULATE(FirstSUm +

SecondSum

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[Quater-Year]))

)

)

scenario 2(Correct) :-

Total_workforce_FLC EURO(M/Y-1) =

if(HASONEVALUE('Axis_Table'[Type]),

SWITCH(VALUES(Axis_Table[Type])

,"Year",CALCULATE(CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date])) +

CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

,TREATAS(VALUES('Axis_Table'[Values])

,'Calendar'[Year_Cal]))

,"Month",CALCULATE(CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date])) +

CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[MonthYear_Cal]))

,"Quarter",CALCULATE(CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date])) +

CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))

,TREATAs(VALUES('Axis_Table'[Values])

,'Calendar'[Quater-Year]))

)

)

[mwegener · Accepted solution]

Hi @Anonymous ,

check this out.

Test Measure2 = 
CALCULATE (
    CALCULATE (
        SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    ),
    FILTER (
        'Calendar',
        CONTAINS (
            VALUES ( Axis_Table[Values] ),
            Axis_Table[Values], SWITCH (
                SELECTEDVALUE ( Axis_Table[Type]),
                "Year", 'Calendar'[Year_Cal],
                "Quarter", 'Calendar'[Quater-Year],
                "Month", 'Calendar'[MonthYear_Cal]
            )
        )
    )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[v-shex-msft]

Hi @Anonymous,

Please check the following blog to know more about variable function usage in Dax calculations:

Using Variables in DAX 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Appriciate your responce.

If variable doesn’t work, is there anything help me out to make my DAX expression efficient?

Thanks,

Saad

[v-shex-msft]

HI @Anonymous ,

Can you please share some dummy data for test? It is hard to modify your formula without any sample data.

In addition, you can also try to use the following formula if it suitable for your requirement:

Total_workforce_FLC EURO(M/Y-1) =
CALCULATE (
    CALCULATE (
        SUM ( MOR_Data[Current month Fully Loaded Costs Euro] )
            + SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
        MOR_Data[STATUS] <> "Contingent",
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    ),
    FILTER (
        ALLSELECTED ( MOR_Data ),
        SWITCH (
            SELECTEDVALUE ( 'Axis_Table'[Type] ),
            "Year", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[Year_Cal] ),
            "Quarter", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[Quater-Year] ),
            "Month", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[MonthYear_Cal] ),
            FALSE ()
        )
    )
)

Regards,

Xiaoxin Sheng

[mwegener]

Hi @Anonymous ,

VAR stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

https://docs.microsoft.com/en-us/dax/var-dax

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Hello, @mwegener ,

Thanks for your reply. I got the understanding of how variable is evaluated.
Is there any way i can optimize my DAX, without creating any external measure ?

Thanks,
Saad

[mwegener]

Hi @Anonymous ,

I played with your idea a bit. It was fun. 😄 

You may download my PBIX file from here.
Hope this helps.

@ShanayaK

You might also be interested in this.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Hello @mwegener 

Thanks for sharing PBIX file, that is the same concept i am working on (Dynamic axis base on Month, Quarter and Year).
I have used a different way to achieve it.
Can you help me out in a measure i have created? Juts to simplify it or write it in an efficient manner without using any external measure. I have uploaded my PBIX, below is the link. (We Transfer file sharing)

https://we.tl/t-xiSdDJhAV2

I have created a "Test Measure" in which I have to repeat same calculation 3 times (Just for Year, Quarter and Month). Can minimize it to just once? using same concept and tables.
@v-shex-msft  appriciate your support as well 🙂

Thanks

[v-shex-msft]

HI @Anonymous ,

After I check your sample file, I found your attribute table does not link to the original table, so your attribute filter does not interact with value table records. I'd like to suggest you take a look at the following blog to know how to achieve dynamic attribute filter:
Dynamic Attributes In A Power BI Report 

Regards,

Xiaoxin Sheng

[Anonymous]

hello @v-shex-msft 

I have achieved Dynamic axis by (Month Quarter and Year) by slicer. From below you can download PBIX file.

https://wetransfer.com/downloads/b020c2fa16ff61a3d38f859f0ce7cca120191230111741/3f18b3

Instead of connecting attribute table to original table, I have use function “TREATAS”

Everything working fine, Just need help in optimizing measure “Total_workforce_FLC EURO(M/Y-1)” . I have to repeat same calculation 3 times. For year quarter and month, is there any way to avoid that using concept I have use?

Thanks

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

I'd like to suggest you use in operator to replace TREATAS function, it also works to compare unrelated fields and list of values:

Total_workforce_FLC EURO(M/Y-1)2 = 
IF (
    HASONEVALUE ( 'Axis_Table'[Type] ),
    CALCULATE (
        CALCULATE (
            SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
            SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
        ),
        FILTER (
            'Calendar',
            SWITCH (
                SELECTEDVALUE ( Axis_Table[Type] ),
                "Year", 'Calendar'[Year_Cal] IN VALUES ( 'Axis_Table'[Values] ),
                "Month", 'Calendar'[MonthYear_Cal] IN VALUES ( 'Axis_Table'[Values] ),
                "Quarter", 'Calendar'[Quater-Year] IN VALUES ( 'Axis_Table'[Values] )
            )
        )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks,

@v-shex-msft  and @mwegener for your support.

Even I have learn new thing out here..!!  🙂 

Regards,

Saad

[mwegener · Accepted solution]

Hi @Anonymous ,

check this out.

Test Measure2 = 
CALCULATE (
    CALCULATE (
        SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    ),
    FILTER (
        'Calendar',
        CONTAINS (
            VALUES ( Axis_Table[Values] ),
            Axis_Table[Values], SWITCH (
                SELECTEDVALUE ( Axis_Table[Type]),
                "Year", 'Calendar'[Year_Cal],
                "Quarter", 'Calendar'[Quater-Year],
                "Month", 'Calendar'[MonthYear_Cal]
            )
        )
    )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.