Power BI - Dynamic Format Strings - Pie Charts

[southgrad10]

Hello, 

I have been asked to make the data labels for a Pie Charts dynamic based on the actual amount.  I have a measure that I am using that is from our Analysis Services tabular cube.  I can change the format to Dynamic and it will let me add the Dynamic Format String, but it doesn't appear to affect the visual and when I go back to click on the Format, there is nothing there and it appears to be blank. 

Does anyone know why it disappears, and more importantly, why is this not working at all?

I have downloaded the most recent version of PBI.  The feature "Dynamic format string for measures" is already checked.

Any clues on how I can get this to work?  Thanks!

The format appears to be gone now after entering it and I cannot click on cell/text box to re-add it.

[OwenAuger · Accepted solution]

Hi @southgrad10 

Just checking is this a correct description of what you're doing?

• Creating a Power BI report with live connection to Analysis Services Tabular
• Trying to create a "report measure" with a dynamic format string

If that's correct, unfortunately report measures cannot have dynamic format strings.

Also, if you switched to DirectQuery for Analysis Services (which is possible for Analysis Services Tabular version 2022+), local model measures with dynamic format strings are not currently possible.

See here for more description of the limitations.

Alternatives that you could consider:

• Create a calculation group in the Analysis Services model to handle dynamic format strings (I think this requires Analysis Services Tabular 2019+).
• If you are using Analysis Services Tabular 2022+, switch to DirectQuery for Power BI Datasets and Analysis Services and create a calculation group in the local model to handle dynamic format strings.

Regards

[suparnababu8]

Hi @southgrad10 

You have to chnage your Format measure like below

---------------------------------------------------------------------

After implemeting of above dynamic format mesure, you can go to Detailed lables-->Values-->Set Display units to None.

After applying these condtions, pls follow.

If you see below,Now it's in million

when you do slice and dice, then it will change like below

[southgrad10]

Yes, I had updated the Display Units to None and that was not a solution to my issue.  Thanks for the tip, though.  I appreciate you trying to help.

[southgrad10]

Update:  I noticed that when I go to enter the Format String after selecting the Dynamic format, supposedly there should be a format already in the editor to overwrite before I paste the switch code, but there isn't anything there, so something is amiss somewhere.  I have the most recent version of PBI.  Should I actually be using an older version for this feature?  Or is there something that I need to be doing differently?  Are Calculated Groups necessary?

[OwenAuger · Accepted solution]

Hi @southgrad10 

Just checking is this a correct description of what you're doing?

• Creating a Power BI report with live connection to Analysis Services Tabular
• Trying to create a "report measure" with a dynamic format string

If that's correct, unfortunately report measures cannot have dynamic format strings.

Also, if you switched to DirectQuery for Analysis Services (which is possible for Analysis Services Tabular version 2022+), local model measures with dynamic format strings are not currently possible.

See here for more description of the limitations.

Alternatives that you could consider:

• Create a calculation group in the Analysis Services model to handle dynamic format strings (I think this requires Analysis Services Tabular 2019+).
• If you are using Analysis Services Tabular 2022+, switch to DirectQuery for Power BI Datasets and Analysis Services and create a calculation group in the local model to handle dynamic format strings.

Regards

[southgrad10]

https://datasavvy.me/2023/04/21/how-to-use-the-new-dynamic-format-strings-for-measures-in-power-bi/

This link shows what I am trying to accomplish.  The measure in Analysis Services is [Product Net Sales].  Then, I created a measure that essentially copies that, so I can update the formatting.  Product Net Sales Formatted = [Product Net Sales].  I can enter the switch Dax code after selecting Dynamic, but before I enter it there should be formatting code already there.

[OwenAuger]

Thanks for the update, understood 🙂

The limitation you're running into is that measures with dynamic format strings can only be created directly in Power BI models, not in Power BI reports as "report level measures".

(see Limitations here).

In the post you linked to, the model/report were in the same PBIX, so dynamic format strings could be defined for measures.

If you have connected to Analysis Services with a live connection, and see "Live connection" in the lower-right of the Power BI Desktop window, you are authoring a report with live connection to another model (rather than authoring a model) and you won't be able to successfully create a measure with a dynamic format string.

Unfortunately, the Power BI Desktop interface deceptively allows you to select "Dynamic" and enter a format string in a live-connected report, but this doesn't actually work. If you were creating a semantic model in Power BI Desktop, you would see the default dynamic format string when you first edited it.

So, currently, the only way to use the dynamic format string feature for measures is to create a Power BI semantic model.

You may be able to make use of calculation groups, depending on Analysis Services version (see post above).

[southgrad10]

Thank you, Owen.  I really appreciate your insights.  I have tried the Calculation Groups in both Tabular Editor as an external tool in Power BI, as well as, in Analysis Services.  I will try again in the morning.  I wonder if the issue is the compatibility level.  I updated it to 1500 from 1200, but does that even matter?

[OwenAuger · Accepted solution]

No worries 🙂

For calculation groups, you need compatibility level 1500 (which should be Analysis Services 2019+), so if you've already updated your Analysis Services model to that you should be good to go with calculation groups.

I'm assuming you were going to create a calculation group in the AS model containing a calculation item with expression

SELECTDMEASURE ( )

and format string expression along the lines of your original post, then apply that as a filter on the visual in the Power BI report.

Please post back if needed 🙂

[southgrad10]

Hi, Owen.

Just wanted to give you an update.  I found a video that showed me how to use the Calculated Group in PBI.

It appears that I have this working now.  When it is thousands, I get a K.  Then, M, for millions.  

Thank you for getting down the correct path!  I will mark your solution as accepted!

BTW - In case anyone is wondering, this is the video that helped me understand Calculated Groups.  Dynamic NUMBER FORMATTING using CALCULATION GROUPS in Power BI (youtube.com)

[OwenAuger]

Glad to hear it!

Looks great, glad to hear you arrived at a working solution! 🙂

[southgrad10]

Thanks, again, for hanging with me on this, Owen.  I really need to get this to work.

This is what I had for the Calculation Group in AS.

When I go to PBI to create a new measure using the original measure, the filter only gives me this option, CalculationItemColumn 1.  Why is it only showing the column and not the actual Calculated Item that I created?