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