Custom Format String based on measure value

[dpc_development]

In Excel, we can format a cell using the following style...

[>=1000000000]#,##0.00,,,"B";[>=1000000]#,##0.00,,"M";##,##0

So, if the value is below a million, it shows the full number, and for millions (Ex: 12,500,000) it will show value in millions (12.5M), etc.

I checked and the above notation does not seem to be supported in Power BI. Power BI Desktop does not allow me to type it and doing so in Tabular Editor shows the condition as a string ("[>=100000"... part)

In reality I have a custom table with a list of measures that I am showing as a drop down, and when the user selects, it plots a different measure on the chart. The values range from a few hundreds to millions. The requirement is to use a different custom format on the tooltips, based on the value.

I realise I can create a new measure and add it to the tooltip section, but my client doesn't want the measure being shown twice. I can use a custom Tooltip Report page, but I am also using Direct Query, and it is slow to load.

[tamnm1601]

I got the same issue like you and seek for a more long-term solution.

The using Calculation Group in Tabular Editor will hugely affect the implicit measures. And it is not stable, sometimes it makes the whole measures disappear without a reason (?!).

Eventually, it's a pity that MS is relying too much on 3rd party application whose free version is unstable at all, especially with huge dataset. This is saying from my own real cases. They should work on these basic features.

😞

[OwenAuger · Accepted solution]

Hi @dpc_development 

It sounds like a Calculation Group to control your number formatting would be useful here.

With a Calculation Group, each Calculation Item has a format string specified by a DAX expression. This format string can be either a constant, or some expression that depends on the measure value itself.

In your case, you may want to consider using the Calculation Group to act as the measure selector, as you could have each Calculation Item specify both the measure and the format string.

Here's a good article on this general topic:

https://exceleratorbi.com.au/dynamic-formatting-of-switch-measures/

Calculation Groups do work with models containing DirectQuery tables, so this approach should work fine in your situation.

Regards,

Owen