Conditional column and DAX

[MrDavidWilliams]

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

[v-xuding-msft]

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]

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]

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]

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]

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.

[MrDavidWilliams]

Here is the .PBIX;

https://www.dropbox.com/s/05g971fco45q21u/Project%20Codes%20TempCurr.pbix?dl=0 

[v-xuding-msft]

Hi @MrDavidWilliams ,

Can the workaround solve your problem? If it helps you, please accept the helpful replies as solutions. If not, please let me know.

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. Kudos are nice too.

[MrDavidWilliams]

Thanks for the workaround. It does work but it will not be accepted by the business user. It adds an additional column per company and repeats for every row. Unfortunately I cannot mark this as the solution.

[v-xuding-msft]

Hi @MrDavidWilliams ,

Unfortunately, I didn't help you. I find two similar ideas about adding measures into the column field of matrix. You could vote it up to get it fixed quickly.

to be able to add measures on rows and columns at the same matrix 

Allow the creater to use a measure as the column headings in a matrix 

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. Kudos are nice too.

[v-xuding-msft]

Hi @MrDavidWilliams ,

Sorry for late reply. After researching, I think you could add another matrix to show the values of "Company" and "Currency". For more details, please see the PBIX below.

LocalUSD

I tried to find a workaround in the original matrix. But I failed. We only can add measures into Values field to implement dynamically filter. 

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.