topic Re: Formatting with a SWITCH function in DAX Commands and Tips

Formatting with a SWITCH function

ratgdillon — Tue, 08 Sep 2020 19:05:49 GMT

Hi,

I am using a SWITCH function to select between cases and revenue as a measure. For cases, the format should be a whole number. For revenue, the format should be currency. This is my formula:

Measure Selection = IF(ISCROSSFILTERED('Measure'[Measure]), SWITCH( TRUE(), VALUES('Measure'[Measure]) = "Gross Revenue", FORMAT([Total Sales], "Currency"), VALUES('Measure'[Measure]) = "Cases", FORMAT([Total Quantity], "0"), BLANK()), BLANK())

where [Total Sales] = CALCULATE(SUM([Sales])) and [Total Quantity] = CALCULATE(SUM([Cases]))

Here is my 'Measure' table:

When I use FORMAT, my table shows blank values:

When I don't use the FORMAT function, my values do not naturally format the way I would like them to:

There are two possible solutions:

1. Find a way to get rid of the blank rows

2. Use a function other than FORMAT to format my values into whole numbers and currency.

Here's some sample data:

Product	Sales	Cases
E.Com - Cakes & Pies/Shells	10	1
E.Com - Cakes & Pies/Shells	30	3
Pound Cake	4000	1000
Pound Cake	1472	368

Thanks!

Re: Formatting with a SWITCH function

lbendlin — Tue, 08 Sep 2020 19:45:23 GMT

In your SWITCH statement you need to also check that there is actually a value, or FORMAT will happily produce an empty string.

Measure Selection = IF(ISCROSSFILTERED('Measure'[Measure]), SWITCH( TRUE(), VALUES('Measure'[Measure]) = "Gross Revenue" && [Total Sales]<>0, FORMAT([Total Sales], "Currency"), VALUES('Measure'[Measure]) = "Cases" && [Total Quantity]>0, FORMAT([Total Quantity], "0"), BLANK()), BLANK())

Re: Formatting with a SWITCH function

amitchandak — Wed, 09 Sep 2020 05:30:37 GMT

ratgdillon , I think you have to use max here

Measure Selection = IF(ISCROSSFILTERED('Measure'[Measure]),
SWITCH(
TRUE(),
Max('Measure'[Measure]) = "Gross Revenue", FORMAT([Total Sales], "Currency"),
max('Measure'[Measure]) = "Cases", FORMAT([Total Quantity], "0"),
BLANK()), BLANK())

But this will make date type as text .

This example can also help you how to handle measure Slicer -https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

Re: Formatting with a SWITCH function

Mond — Wed, 17 Mar 2021 21:15:09 GMT

Hi,

I added "ISCROSSFILTERED" and checking actually a value exists in my switch funtion but format option didn't work out.

My final measure is dependant on multiple measures.

Are there any steps needs to be taken.

Thank you in advance.

Re: Formatting with a SWITCH function

NilR — Fri, 07 May 2021 14:59:10 GMT

Why this is not working for stacked Chart?

Re: Formatting with a SWITCH function

NilR — Fri, 07 May 2021 15:01:13 GMT

I have Stacked Chart, but can't get this working. do you have any other solution?

Re: Formatting with a SWITCH function

Deevo_ — Thu, 03 Nov 2022 05:42:49 GMT

Hi lbenlin,

I am new to DAX and I have tried to implement your solution into my query, but I am not sure where I am going wrong? Below is my selector measure and switch measure that I have created as measures. This "selector measure" is used as a toggle between the different metrics ($ and FTE). I then put on a "Card" visual the "Switch Measure:" which changes according to the toggle.

Problem:

Everytime i switch to the Currency metric, the Symbol does not display at all. Can you pleae help me modify my measures to allow the Currency symbol to display when only the $ metric is selected?

Selector Measure:

Selector = UNION(ROW("Type","Hrs."),ROW("Type","Wks."),ROW("Type","FTE"), Row("Type","$."))

Switch Measure:

Cost Selection = var selectitem=IF(HASONEVALUE(Selector[Type]),VALUES(Selector[Type]),BLANK())

return

SWITCH(selectitem,

"$", [Costing ($)],

"FTE", [Costing (FTE)],

BLANK())

Re: Formatting with a SWITCH function

lbendlin — Thu, 03 Nov 2022 14:09:43 GMT

Deevo_ please create a new post with your question

Re: Formatting with a SWITCH function

ahmadov_10 — Wed, 22 Mar 2023 09:31:48 GMT

Hello, I'm looking a measure that change the format the format of salesAmount dynamically based on currency selection slicer and you give me this code : Sales Amount Selected Currency = VAR SelectedCurrency = SELECTEDVALUE('SlicerTable'[Currency]) RETURN IF(SelectedCurrency = "Local", SUM('SalesTable'[SalesAmount]), IF(SelectedCurrency = "Euro", FORMAT(SUM('SalesTable'[SalesAmount_EUR]), "€#,##0.00"), IF(SelectedCurrency = "Dollar", FORMAT(SUM('SalesTable'[SalesAmount_EUR]), "$#,##0.00") ) ) ) the problem when i use this measure, it show me a blank values out of the range sales dates (my sales dates years 2022 and 2023 before using the measure , after using the measure i found year 2014 and 2015 with blank values). how we can solve the problem ?

Re: Formatting with a SWITCH function

lbendlin — Wed, 22 Mar 2023 11:19:05 GMT

ahmadov_10 please create a new post. Include sample data.