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:

 

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:

ProductSalesCases
E.Com - Cakes & Pies/Shells101
E.Com - Cakes & Pies/Shells303
Pound Cake40001000
Pound Cake1472368

 

Thanks!

  • 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())

9 Replies

  • 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.

    • NilR's avatar
      NilR
      Post Patron
      5 years ago

      Why this is not working for stacked Chart?

    • Deevo_'s avatar
      Deevo_
      Resolver I
      3 years ago
      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())
  • 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?