Forum Discussion
Power BI - Dynamic Format Strings - Pie Charts
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.
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
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 🙂
11 Replies
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?
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
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.
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
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.
