topic Re: Dynamic number format in DAX Commands and Tips

Dynamic number format

AhmadBakr — Sun, 07 Jul 2024 09:40:33 GMT

Hi,

I have a measure [PO_Amount] which migh show wide range of numbers from thousands to billions. I chose dynamic format for it and entered the below in the "Format" formula bar:

VAR kil = 10^3

VAR mil = 10^6

VAR bil = 10^9

RETURN

SWITCH(

TRUE(),

[PO_Amount] >= bil, FORMAT([PO_Amount]/bil, "0.00B"),

[PO_Amount] >= mil, FORMAT([PO_Amount]/mil, "0.00M"),

[PO_Amount] >= kil, FORMAT([PO_Amount]/kil, "0.00K"),

FORMAT([PO_Amount], "0.00")

)

To my surprize, it does not show the decimal point at all, e.g. if the value is 1,123,456,789 and should show as 1.12B, it appears 112B

Re: Dynamic number format

OwenAuger — Sun, 07 Jul 2024 11:45:14 GMT

Hi AhmadBakr

The dynamic format string expression should be written to return a format string, rather than returning the formatted value itself.

For example, the format string expression should return values such as these to format billions, millions or thousands:

"0,,,.00B" "0,,.00M" "0,.00K"

Here is a suggested rewritten version of the format string expression:

VAR kil = 10^3 VAR mil = 10^6 VAR bil = 10^9 VAR amount = [PO_Amount] RETURN SWITCH( TRUE(), amount >= bil, "0,,,.00B", amount >= mil, "0,,.00M", amount >= kil, "0,.00K", "0.00" )

You could add "#," to the start of each string for digit grouping:

VAR kil = 10^3 VAR mil = 10^6 VAR bil = 10^9 VAR amount = [PO_Amount] RETURN SWITCH( TRUE(), amount >= bil, "#,0,,,.00B", amount >= mil, "#,0,,.00M", amount >= kil, "#,0,.00K", "#,0.00" )

Does the above work for you?

Re: Dynamic number format

AhmadBakr — Tue, 09 Jul 2024 08:40:57 GMT

Hi OwenAuger

Thank you for your response.

In fact I tried something similar and did not work.

I tried the code you suggested and for a value 17.46bn (as auto formatted), it returned 0.00bnB (!)
I am not sure what might be missing.

Re: Dynamic number format

OwenAuger — Tue, 09 Jul 2024 09:43:04 GMT

Ah right, with the Card visual (and certain other situations) make sure the visual's number format settings are not conflicting with the measure's number format.

For a Card, set

Callout value > Display units = None
Callout value > Value decimal places = Auto

Does that fix it?

Re: Dynamic number format

AhmadBakr — Tue, 09 Jul 2024 10:01:47 GMT

OwenAuger

Thanks a lot, this solved it. I was tuning all these (code plus format) but your code together with the right format setting did the job.

Much appreciated, you save me double the number of measures to write a dedicated format measure for every numerical measure 🙂

Thank you.

Re: Dynamic number format

AhmadBakr — Tue, 09 Jul 2024 10:48:11 GMT

OwenAuger

Is there a way to reference a generic measure name, so I can assign the dynamic format code to a measure and use this measure name in the dynamic format string of any measure without the need to change the name of the measure to be formatted (highlighted red below)?

VAR kil = 10^3
VAR mil = 10^6
VAR bil = 10^9
VAR input = [Amt]
RETURN
SWITCH(
TRUE(),
input >= bil, "#,0,,,.00B",
input >= mil, "#,0,,.00M",
input >= kil, "#,0,.00K",
"#,0.00"
)

Re: Dynamic number format

OwenAuger — Tue, 09 Jul 2024 12:13:02 GMT

AhmadBakr

Good question 🙂

If a format string depends on a measure's value (as it does in your example), then I don't believe it's possible to assign the format string expression to a separate measure and then reference this measure within format string expressions of individual measures.

The function SELECTEDMEASURE() can be used to generically evaluate the "current" measure, but it only works when used directly in a format string expression, not in a measure referenced by a format string expression.

So the options I can suggest for applying this same format string to multiple measures are:

In your above expression, change [Amt] to SELECTEDMEASURE() , and replicate the dynamic format string code for every measure. This can be done relatively quickly with Tabular Editor (by selecting multiple measures and changing the dynamic format string expression property).
Rather than using measure dynamic format strings, instead create a calculation group containing a calculation item with the appropriate format string expression using SELECTEDMEASURE(). This calculation item could then be applied as a filter to control the formatting of any number of measures at visual/page/report level.
See this article for example.

Hopefully that is of some help 🙂

Re: Dynamic number format

AhmadBakr — Tue, 09 Jul 2024 13:56:53 GMT

The first solution appeals to me more and worked. you are right, when I assigned the format code to a separate measure and used it to format the Amt measure, didn't work, althought I tried (for the fun of it) to use VALUE() but no! May be later we can get some function like EXCEL's INDIRECT() 🙂

And thank you for the article as well. This is super useful.

Thanks a lot for your responses and support.