topic Re: DAX FORMAT function Alternative - Switch Statement in DAX Commands and Tips

DAX FORMAT function Alternative - Switch Statement

COIL-ibesmond — Wed, 28 Feb 2024 18:04:06 GMT

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

Re: DAX FORMAT function Alternative - Switch Statement

lbendlin — Thu, 29 Feb 2024 01:02:24 GMT

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

Re: DAX FORMAT function Alternative - Switch Statement

COIL-ibesmond — Thu, 29 Feb 2024 01:34:03 GMT

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.

Re: DAX FORMAT function Alternative - Switch Statement

lbendlin — Thu, 29 Feb 2024 01:54:29 GMT

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

Re: DAX FORMAT function Alternative - Switch Statement

talespin — Thu, 29 Feb 2024 07:38:31 GMT

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

Re: DAX FORMAT function Alternative - Switch Statement

COIL-ibesmond — Fri, 01 Mar 2024 00:02:23 GMT

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.

Re: DAX FORMAT function Alternative - Switch Statement

COIL-ibesmond — Fri, 01 Mar 2024 00:01:53 GMT

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

Re: DAX FORMAT function Alternative - Switch Statement

COIL-ibesmond — Fri, 01 Mar 2024 00:05:04 GMT

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

Re: DAX FORMAT function Alternative - Switch Statement

talespin — Fri, 01 Mar 2024 03:59:38 GMT

hi COIL-ibesmond

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