Dynamic String Format

[SuryaDave]

Hello experts,

My question to relates to the dynamic string that are now available in Power BI. Btw I am using latest Power BI desktop and my original file is on our cloud server ( premium ) 

I will explain my issue / question with below scenario - I have attached link to the sample file as well.

So, basically in the sample file I have 2 measures, 1 has data type of numbers & other measure is percentage (%).  I have created measure & created a chart.  The measure created uses  "dynamic string format". 

So, if you go the format part of the measure - you will see that I have manually copy pasted name of the metric and assigned format to it ( with selectedvalue ) . So, in summary metric name and format are hardcoded in format part.

My question is ....  in the example file there are only 2 measures so it was easy to hardcode but in real life scenario - I have about 180 kpi metrics. So writing or copy pasting & maintaining hardcoded metric name and hardcoded dynamic format is very difficult. 
Is there a solution that I can create a table inside Power BI itself or outside power bi and then dynamic string to refer to that table > read the name of the KPI metric > and then read the dynamic format I want to appy to that KPI metric and apply that dynamic format ??
Link to PBI File  

[Ashish_Mathur]

Hi,

While this is not a direct answer to your question, my suggestion would be to transform your data in a form that creates seperate columns for each metric.  This M code does that

let
    Source = Excel.Workbook(File.Contents("C:\Users\Ashish Mathur\Downloads\Dynamic String Question.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State Level Metric", type text}, {"Month End Date", type date}, {"Numberator", Int64.Type}, {"Denominator", Int64.Type}, {"Value", type number}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Numberator", "Denominator", "Column6", "Column7", "Column8"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"State Level Metric"]), "State Level Metric", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"State wide concern raised - Type A - %", Percentage.Type}})
in
    #"Changed Type1"

Hope this helps.

[danextian]

Hi @SuryaDave 

Instead of hardcoding the metric name, you can perhaps check whether the metric name contains a certain string and return the format string based on that condition.

IF ( CONTAINSSTRING ( 'table'[metric name], "%" ), "#0.0%", "##0" )

You also use a physical table that contains the mmetric name and the format string and just simply reference the string itself. You can another to column to the table where the metric names are.

SELECTEDVALUE ( 'table'[format string] )

[SuryaDave]

Thanks @danextian  for your response. 

Apologies for very late response  - I was travelling and then got busy with few urgent items.

Regarding your response - I really liked your idea  of using selected value 

SELECTEDVALUE ( 'table'[format string] )

However, somehow I was not able to get it to work just like that. 
So what I did was  created look up table - see screenshot below 

 

I then joined then using may to one with my original table  and then as last step created below format string & that is working perfectly.

@danextian - can you please see if that is something that you think is a solution  or you have bettter suggestion.

I will for your response & accordingly I will accept suggestion as solution to make sure that someone having the same problem can find the correct solution throught this forum.

I would also like to thanks @lbendlin , @Ashish_Mathur & @v-aatheeque  for your contribution and suggestion. Feel free to provide further input on the basis of my these post and screenshots I have attached.

Thanks

[v-aatheeque]

Hi @SuryaDave 

Just following up regarding your question about using dynamic string formatting in Power BI , thanks for sharing the sample file and context!

You're currently using the latest Power BI Desktop and working with a file hosted on your Premium cloud server. In your example, you’ve manually hardcoded the metric names and formats using SELECTED VALUE for just two measures one numeric and one percentage. That works fine for small cases, but as you mentioned, managing this manually for 180+ KPI metrics would be extremely difficult.

Your idea of creating a table (either inside Power BI or externally) to store KPI names and their corresponding formats is absolutely on the right track!

You can then use DAX to dynamically reference this table and apply the correct format string based on the selected KPI.

Let me know if you’d like to explore that further!

Thanks again for being such an engaged member of the Microsoft Fabric Community

[v-aatheeque]

Hi @SuryaDave 

I wanted to follow up regarding your inquiry about using dynamic string formatting in Power BI. Thank you for sharing the sample file and providing context!

I see that you're using the latest version of Power BI Desktop and working with a file hosted on your Premium cloud server. In your example, you've hardcoded the metric names and formats using SELECTED VALUE for just two measures one numeric and one percentage. While this approach works for a small number of cases, I understand that managing it manually for over 180 KPI metrics would be quite challenging.

Your suggestion to create a table (either within Power BI or externally) to store KPI names along with their corresponding formats is an excellent idea!

You can utilize DAX to dynamically reference this table and apply the appropriate format string based on the selected KPI.

If you still facing issue , I’d be happy to help you explore this further!

Thank you once again for being such an active member of the Microsoft Fabric Community!

[lbendlin]

You are moving into "Calculation Groups" territory. Read about those and see if they are a better fit for your scenario.

[SuryaDave]

thanks @lbendlin  for your response. I actually checked couple of videos on calculation group but I noticed that it still requires formatting to be hardcoded. 
My question is how can I create a reference table or look up table - that I can point to for dynamic string to read from apply formatting reading from that table ...

If you think that calculation group can solve that - any chance you can demo that with exmple - if that's ok ? 
Thanks

[lbendlin]

What you are looking for (dynamic evaluation) is sadly (or thankfully, depending on your viewpoint) not supported in Power BI Desktop or the UI in the service, namely the ability to run EVALUATE.  (It is possible to do that in external tools, and in Embedded scenarios)

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com