Dynamic DAX Number Format

Question

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.

Answer 1

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.

Answer 2

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.