Dynamic DAX Number Format

12
votes

I want to dynamically change the number format of a DAX measure, based on a dimension value (or indeed, based on the order of magnitude of the measure value).

I understand I can use SWITCH and FORMAT, as demonstrated by Kaspar De Jonge here: https://www.kasperonbi.com/dynamic-format-using-dax/

Here's an example of the type of measure I'm creating:

My Measure:=IF ( 
    HASONEVALUE ( dimMeasureType[Measure Type] ), 
    SWITCH ( VALUES ( dimMeasureType[Measure Type] ),
        "Total Cost", FORMAT ( [Total Cost], "#,##0, k" ),
        "Cost Per Unit", FORMAT ( [Cost Per Unit], "#,##0.00" ),
        "Cost % Sales", FORMAT ( [Cost % Sales], "0.00%" ),
        BLANK()
    ),
    BLANK()
)

But this technique returns text measures. I need to be able to chart my measures, so I do not want to convert them to text. Is there another technique for dynamically changing a measure number format, without converting to a string?

If it makes a difference, I'm working in SSAS-Tabular on SQL Server 2016 BI.

2
powerbidaxssas-tabular
Did you try this method already? If so - can you post the format strings you used? – dybzon
Yes, I've tried. I can easily format the measures in the way I want - but the problem is that using FORMAT returns text. I want number values, formatted dynamically. – Olly
Can you try some pre-defined numeric formats msdn.microsoft.com/en-us/query-bi/dax/… – M O'Connell
Regardless of whether the format is custom or pre-defined, the FORMAT function returns text, not numbers. – Olly
Not possible man, vote it up though. I've been looking for a work around to this for quite a while. If you figure something out PLEASE post it here. – StelioK

2 Answers

5
votes

I don't believe this is currently possible, but it a popular feature request that will hopefully be implemented in the future.

I recommend voting and commenting on the idea I linked to in order to add your support.

1
votes

A workaround is to create multiple measures and add them all to your chart. Depending on your dimension value only one measure returns values, all other measures return BLANK() and are not displayed in your chart. You can give them the same display name by adding whitespace to the end of their names:

My Measure:=IF ( 
    SELECTEDVALUE( dimMeasureType[Measure Type] ) = "Total Cost", 
    [Total Cost],
    BLANK()
)
[My Measure ]:=IF ( 
    SELECTEDVALUE( dimMeasureType[Measure Type] ) = "Cost Per Unit", 
    [Cost Per Unit],
    BLANK()
)
[My Measure  ]:=IF ( 
    SELECTEDVALUE( dimMeasureType[Measure Type] ) = "Cost % Sales", 
    [Cost % Sales],
    BLANK()
)

This has some drawbacks though:

  • The chart legend shows all measures, even if all their values are BLANK().
  • The y-Axis of your chart has the same format as the first measure in its 'Values' section.