Forum Discussion

ratgdillon's avatar
ratgdillon
Frequent Visitor
5 years ago
Solved

Formatting with a SWITCH function

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: ...
  • lbendlin's avatar
    lbendlin
    5 years ago

    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())
lbendlin's avatar
lbendlin
Super User
5 years ago

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())
Mond's avatar
Mond
Helper III
5 years ago

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.