Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 do...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
mwegener
Most Valuable Professional
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.
    Anonymous  appriciate your support as well 🙂

     

    Thanks

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        hello Anonymous 

         

        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