DAX FORMAT function Alternative - Switch Statement

[COIL-ibesmond]

I'm trying to build a switch statement, but I'm running into a formatting problem.

I'm using FORMAT(ActualVsBudgetPerc, "Percent" ) but this turns my value into a string which I want to avoid because I can't sort or rank the measure once it is turned into text.  Here is my switch statement. I won't define the variables in the statement.

VAR Result = SWITCH(
    SELECTEDVALUE([id]), 
    1, Sales, 
    8, ActualVsBudget, 
    5, FORMAT(ActualVsBudgetPerc, "Percent"),
    4, SalesLY, 
    9, SalesLYVar,
    6, FORMAT(SalesLYPerc, "Percent"),
    Sales
)

RETURN
Result

Is there an alternative to FORMAT to get the percentage value to appear without turning it into a text string?

Thanks,

COIL-ibesmond

[COIL-ibesmond]

Decided to go old-school and dropped the % from the value and multiplied it by 100. 

[talespin · Accepted solution]

Hi @COIL-ibesmond 

Under such scenario, I usually create two measures, one that only returns numeric data types and other one with format applied for display purpose just like an Elephant, one set for display teeth and another real set for chewing. 😄

[COIL-ibesmond]

Thanks for the response.  I though about making duplicate measures, but it would add too many new measure to the report, which is already over 1 GB.  

[talespin]

hi @COIL-ibesmond 

You're welcome. Duplicate measure are a clutter but measures do not add to size of report.

[lbendlin]

have you considered using the built-in implicit formatting in Power BI?

[COIL-ibesmond]

Yes, I cannot format the entire measure because some values are dollar amounts and some are percentages.  The measure calculates a row using the switch statement above.   

[lbendlin]

ah, that's an important piece of information.

Enably Dynamic Measure Formatting and learn how to use it.

Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

[COIL-ibesmond]

Tried this, but wouldn't work.  Appreciate the response.