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

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

NilR's avatar
NilR
Post Patron
5 years ago

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