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())
ahmadov_10's avatar
ahmadov_10
Helper II
3 years ago

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 ?

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    ahmadov_10 please create a new post. Include sample data.