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.

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

      Deevo_ please create a new post with your question

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