powerbidaxssas-tabular

Dynamic DAX Number Format


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.


Solution

  • 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