Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
COIL-ibesmond
Helper I
Helper I

DAX FORMAT function Alternative - Switch Statement

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

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

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

View solution in original post

8 REPLIES 8
COIL-ibesmond
Helper I
Helper I

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

talespin
Solution Sage
Solution Sage

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

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.  

hi @COIL-ibesmond 

 

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

lbendlin
Super User
Super User

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

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.   

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors