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())
NilR's avatar
NilR
Post Patron
5 years ago

Why this is not working for stacked Chart?