13

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.

Olly
  • 7,749
  • 1
  • 19
  • 38
  • Did you try this method already? If so - can you post the format strings you used? – dybzon Aug 16 '17 at 12:33
  • 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 Aug 16 '17 at 12:37
  • Can you try some pre-defined numeric formats https://msdn.microsoft.com/en-us/query-bi/dax/pre-defined-numeric-formats-for-the-format-function – M O'Connell Jun 17 '18 at 12:40
  • Regardless of whether the format is custom or pre-defined, the `FORMAT` function returns text, not numbers. – Olly Jun 17 '18 at 14:08
  • 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 Sep 13 '18 at 22:12
  • It is possible with calculation groups, do you know it? – Przemyslaw Remin Jan 13 '22 at 14:52

3 Answers3

6

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
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.
Christian Welsch
  • 424
  • 2
  • 10
0

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

Olly
  • 7,749
  • 1
  • 19
  • 38