Forum Discussion

SuryaDave's avatar
SuryaDave
Icon for Helper I rankHelper I
1 year ago
Solved

Dynamic String Format

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 )  ...
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    Hi SuryaDave 

    Just to help further (and in case it’s helpful for anyone else referencing this thread), I’ve created a sample .pbix file that demonstrates the SELECTEDVALUE pattern

    Hope it helps!!

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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] )

 

  • v-aatheeque's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      v-aatheeque
      Icon for Community Support rankCommunity Support
      1 year ago

      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!

  • SuryaDave's avatar
    SuryaDave
    Icon for Helper I rankHelper I
    1 year ago

    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's avatar
      v-aatheeque
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi SuryaDave 

      Just to help further (and in case it’s helpful for anyone else referencing this thread), I’ve created a sample .pbix file that demonstrates the SELECTEDVALUE pattern

      Hope it helps!!

      • SuryaDave's avatar
        SuryaDave
        Icon for Helper I rankHelper I
        1 year ago

        Thanks for posting pbi file with example to clarify ....

         

        Also thanks to danextian  for suggesting this option earlier

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      1 year ago

      For SELECTEDVALUE (['table'[format string] ) to work string to work, it must be in the same table as those used in the visual.