Forum Discussion

MrDavidWilliams's avatar
MrDavidWilliams
Frequent Visitor
6 years ago

Conditional column and DAX

I need some help with a conditional column and DAX; I have an expense report with 2 measures - 'Expenses Local' and 'Expenses USD'. I have created a single measure – ‘Expenses’ using a SWITCH on a d...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi MrDavidWilliams ,

I think you could try change the function of "VALUES" to "MAX". I created a sample that you could reference.

Currency Column2 = 
VAR CurrencySelection =
    SELECTEDVALUE ( 'Currency'[Currency] )
RETURN
    SWITCH ( TRUE(),
        CurrencySelection = "Local" && MAX('Project Codes'[Currency]) = "AUD", "AUD",
        CurrencySelection = "Local" && MAX('Project Codes'[Currency]) = "GBP", "GBP",
        CurrencySelection = "Local" && MAX('Project Codes'[Currency]) = "ZAR", "ZAR",
        CurrencySelection = "Local" && MAX('Project Codes'[Currency]) = "USD", "USD",
        CurrencySelection = "USD", "USD",
        "-1"
    )

If it is not what you want, please share a sample data model that make us understand clearly.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MrDavidWilliams's avatar
MrDavidWilliams
Frequent Visitor
6 years ago

Changing to 'MAX' doesn't change anything. It still shows the '-1'.

I require a column and not a measure.

Data Model

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi MrDavidWilliams ,

    Calculated column is static. It is unavailable to filter data dynamically. Measures can implement show different values based on the context.

    Can I ask you why you want to create a column rather than a measure? Do you want to create relationships between it and calendar table?

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MrDavidWilliams's avatar
      MrDavidWilliams
      Frequent Visitor
      6 years ago

      Thanks for letting me know that conditional columns are not dynamic. I don't think I can use a measure in this scenario because I already have 2 measures in the matrix - I would need to nest these 2 measures in the 'currency measure'. I don't think this is possible.

       

      See example of matrix output below:

      Matrix layout

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi MrDavidWilliams ,

        Can you please share a dummy file? I can't reproduce the matrix that you shared. I think maybe we can find a workaround using the .pbix file to implement your request.

         

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.