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 🙂
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 🙂
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)
Glad to hear it!
Looks great, glad to hear you arrived at a working solution! 🙂
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?