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 disconnected table currency slicer. The report covers multiple regions and hence the requirement for currency.

I currently have a disconnected slicer with either 'Local' or 'USD'. In the matrix column headers I have region and currency nested. It works fine with the slicer and converts the values and column headers accordingly.

However, I have a requirement to display the actual currency in the column headers when 'Local' is selected.

I have tried using a new column with a switch function using an 'AND'/'&&' but I cannot get it working.

NOTE: The measure values are converting correctly – it is just the currency column headers that I would like to change when the ‘Local’ option is selected on the slicer.

There is no error when creating the new column but it always returns the ‘-1’ option when I select ‘Local’ on the slicer.

 

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

 

 

SlicerRequired output

Need Help

10 Replies

  • 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.