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.

Araarab

Did you try this method already? If so - can you post the format strings you used? – Dibble

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. – Araarab

Can you try some pre-defined numeric formats msdn.microsoft.com/en-us/query-bi/dax/… – Infeasible

Regardless of whether the format is custom or pre-defined, the FORMAT function returns text, not numbers. – Araarab

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. – Ryals

It is possible with calculation groups, do you know it? – Summer

Answer 1

This is now possible in Power BI using dynamic format strings.

Microsoft documentation: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

Araarab

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.

Cusack

Answer 3

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.

Illegal