topic Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text in DAX Commands and Tips

Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

sparta1000 — Tue, 20 Sep 2022 00:17:13 GMT

The DAX below uses calculation IDs from a disconnected table to assign measures to sections of a P&L's actuals. However, the most trivial part of the code i.e., the two references to the FORMAT function, eliminates further calculations, such as variance to plan for the those two line items. For some reason, FORMAT was designed to convert numbers to text, which is the last thing I can imagine ever wanting as no further calculations are possible at that point, which defeats the entire purpose of DAX. Is there an equivalent function that allows you to simply apply a custom format to a number and actually have it remain a number?

02.111measure Actuals Complete P&L:=VAR AssignIDsToMeasures = SWITCH(TRUE(),

MAX(dPnLSkeleton[CalculationID]) = 1, [02.101m CC Actuals Revenue],

MAX(dPnLSkeleton[CalculationID]) = 2, [02.102m CC Act CoR],

MAX(dPnLSkeleton[CalculationID]) = 3, [02.103m CC Act Gross Profit],

MAX(dPnLSkeleton[CalculationID]) = 4, FORMAT([02.104m CC Act Gross Profit %] , "0.0%") ,

MAX(dPnLSkeleton[CalculationID]) = 5, [02.105m CC Act Opex S&M],

MAX(dPnLSkeleton[CalculationID]) = 6, [02.106m CC Act Opex R&D],

MAX(dPnLSkeleton[CalculationID]) = 7, [02.107m CC Act Opex G&A],

MAX(dPnLSkeleton[CalculationID]) = 8, [02.108m CC Act Opex S&M, R&D, G&A],

MAX(dPnLSkeleton[CalculationID]) = 9, [02.109m CC Act EBITDAR],

MAX(dPnLSkeleton[CalculationID]) = 10, FORMAT([02.110m CC Act EBITDAR %], "0.0%") )

RETURN

IF (AssignIDsToMeasures = 0, BLANK(),

AssignIDsToMeasures)

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

amitchandak — Tue, 20 Sep 2022 02:05:07 GMT

sparta1000 , better you opt for calculation group

https://www.sqlbi.com/articles/dynamic-format-strings-with-calculation-groups/

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

sparta1000 — Tue, 20 Sep 2022 05:14:29 GMT

Thank you for the quick response, Amitchandak. I very much appreciate it. I didn't understand any of the concepts in the article or video but will begin investigating Tabular Editor and calculation groups going forward. In the meantime, I'll post if I find a simple solution to what should be a trivial problem. I also should have mentioned this solution is required for PowerPivot not Power BI.

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

tamerj1 — Tue, 20 Sep 2022 03:40:10 GMT

Hi sparta1000

please try

02.111measure Actuals Complete P&L := VAR Selection = MAX ( dPnLSkeleton[CalculationID] ) VAR AssignIDsToMeasures = SWITCH ( Selection, 1, [02.101m CC Actuals Revenue], 2, [02.102m CC Act CoR], 3, [02.103m CC Act Gross Profit], 4, [02.104m CC Act Gross Profit %], 5, [02.105m CC Act Opex S&M], 6, [02.106m CC Act Opex R&D], 7, [02.107m CC Act Opex G&A], 8, [02.108m CC Act Opex S&M, R&D, G&A], 9, [02.109m CC Act EBITDAR], 10, [02.110m CC Act EBITDAR %] ) RETURN IF ( AssignIDsToMeasures <> 0, IF ( Selection IN { 4, 10 }, FORMAT ( AssignIDsToMeasures, "0.0%" ), AssignIDsToMeasures ) )

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

sparta1000 — Tue, 20 Sep 2022 13:29:36 GMT

Thank you tamerj1 -- your solution looks great! I'll test it out later this morning and post an update. I should mention that users don't select any of these line items as the entire framework must be present at all times for the P&L statement to appear in its entirety -- so not sure if that makes a difference but I'll test.

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

sparta1000 — Tue, 20 Sep 2022 16:41:25 GMT

Thank you tamerj1 -- I like your thought process with the code, but I didn't explain the issue very well. Your code appears to apply to typical scenarios where users will be making different selections. In my case, there are no selections; everything's set in stone from a report line item perspective.

Nevertheless, the whole issue is how to get around FORMAT's inexplicable conversion of numbers to text. If FORMAT didn't change the data type to text, the simple variance math would be executed and there would be no issue. That's why this is so frustrating.

To make the issue as transparent as possible, I'll isolate the behavior of the Variance % measure that is driven by earlier measures that contain FORMAT. The Variance % measure materializes two line items on the visual that must be formatted as % and yet ideally still behave like numbers. But because FORMAT in supporting measures converts numbers to text, in order to avoid a DAX 'can't do math on text' error message, the Variance % measure is currently written to effectively blank out (via empty string) the two problematic line items.

Below is the the code for the Variance % measure that produces the report above.

07.311m Var % Act vs Bud:=SWITCH (TRUE(),
MAX( dPnLSkeleton[CalculationID]) = 1, [07.301m CC Var % Act Rev Less Threshold Rev],
MAX( dPnLSkeleton[CalculationID]) = 2, [07.302m CC Var % Threshold CoR Less Act CoR],
MAX( dPnLSkeleton[CalculationID]) = 3, [07.303m CC Var % Act Gross Profit Less Threshold Gross Profit],
MAX( dPnLSkeleton[CalculationID]) = 4, "",
MAX( dPnLSkeleton[CalculationID]) = 5, [07.305m CC Var % Threshold S&M Less Act S&M],
MAX( dPnLSkeleton[CalculationID]) = 6, [07.306m CC Var % Threshold R&D Less Act R&D],
MAX( dPnLSkeleton[CalculationID]) = 7, [07.307m CC Var % Threshold G&A Less Act G&A],
MAX( dPnLSkeleton[CalculationID]) = 8, [07.308m CC Var % Threshold Opex SMRDGA Less Act Opex SMRDGA],
MAX( dPnLSkeleton[CalculationID]) = 9, [07.309m CC Var % Act EBITDAR Less Threshold EBITDAR],
MAX( dPnLSkeleton[CalculationID]) = 10, "")

So I'm still left wondering how to get around FORMAT's conversion of numbers to text in the supporting measures so that the Variance % measure works for all line items... and no more empty string workaround or "can't do math on text" error.

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

tamerj1 — Tue, 20 Sep 2022 17:07:06 GMT

sparta1000

There is no FORMAT function in this measure, however, the empty sting "" will generate error when performing calculations. When referring to this measure in other formulas you need to wrap it with VALUE function. This will eliminate the effect of the empty string ""

However, unless you want to force showing the blank cell in your matrix (which Idon't think is required in your case) I would suggest just to not use the empty string in your formula. In fact the whole condition is not require as the result will be blank by default.

07.311m Var % Act vs Bud := SWITCH ( MAX ( dPnLSkeleton[CalculationID] ), 1, [07.301m CC Var % Act Rev Less Threshold Rev], 2, [07.302m CC Var % Threshold CoR Less Act CoR], 3, [07.303m CC Var % Act Gross Profit Less Threshold Gross Profit], 5, [07.305m CC Var % Threshold S&M Less Act S&M], 6, [07.306m CC Var % Threshold R&D Less Act R&D], 7, [07.307m CC Var % Threshold G&A Less Act G&A], 8, [07.308m CC Var % Threshold Opex SMRDGA Less Act Opex SMRDGA], 9, [07.309m CC Var % Act EBITDAR Less Threshold EBITDAR] )

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

sparta1000 — Tue, 20 Sep 2022 19:57:48 GMT

Solved: Convert text with percentage symbol to number (DAX... - Microsoft Power BI Community

It looks like I'm encountering the same thing addressed in this post. I'm going to try and adapt the thought process and post if I have success. I appreciate the time you've taken on this very much.

Re: Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

sparta1000 — Tue, 20 Sep 2022 23:17:16 GMT

Well no luck.

If I write "Test Measure":= 1 + the measure below:

02.111mTEST CC Act "data" Complete P&L Expand:=VAR AssignIDsToMeasures = SWITCH(TRUE(),
MAX(dPnLSkeleton[CalculationID]) = 1, [02.101m CC Actuals Revenue],
MAX(dPnLSkeleton[CalculationID]) = 2, [02.102m CC Act CoR],
MAX(dPnLSkeleton[CalculationID]) = 3, [02.103m CC Act Gross Profit],
MAX(dPnLSkeleton[CalculationID]) = 4, FORMAT( [02.104m CC Act Gross Profit %] , "0.0%" ) ,
MAX(dPnLSkeleton[CalculationID]) = 5, [02.105m CC Act Opex S&M],
MAX(dPnLSkeleton[CalculationID]) = 6, [02.106m CC Act Opex R&D],
MAX(dPnLSkeleton[CalculationID]) = 7, [02.107m CC Act Opex G&A],
MAX(dPnLSkeleton[CalculationID]) = 8, [02.108m CC Act Opex S&M, R&D, G&A],
MAX(dPnLSkeleton[CalculationID]) = 9, [02.109m CC Act EBITDAR],
MAX(dPnLSkeleton[CalculationID]) = 10, FORMAT( [02.110m CC Act EBITDAR %] , "0.0%") )
RETURN
IF (AssignIDsToMeasures = 0, BLANK(),
AssignIDsToMeasures)

It errors out because FORMAT converts the data type from number to text (why you would ever want your number data type to suddenly change to text and therefore instantly kill any follow on calculations I have no idea). All I want is to nullify this inexplicable behavior from FORMAT. I just need it to leave the data type alone - but with a little tweak to the number format in two cases. I can't figure out how to apply VALUE or CONVERT to the code to achieve this despite considerable efforts.

If anyone has any ideas please let me know. Sucks being defeated by such a trivial issue. At this point I'd be happy with an explanation as to why FORMAT was designed to be so destructive -- what could possibly be the point?