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