Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom Format String

Hi all,

 

Is there a way to format a column that is type: decial number so that special values (like null, blank, or infinity) will return a text value of "N/A"? When trying to find one of these special values and replace with N/A, it is not allowed because the column is of a number format and does not work with text replacements. 

 

Perhaps there is a way to format the string on the Model page under properties when the measure is selected... I know that Tableau has this functionality, and it seems like it would be a relatively common one that Power BI should have. If not, has anyone found a workaround for this special instance?

 

Thank you in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I found a solution! For the metrics Bloomberg.EPS and Bloomberg.Revenue that come from the data source, I created measures that look like the following: 

    The format was reverted back to text, but I was easily able to choose the correct currency type and decimal places necessary without interrupting an "N/A" output. 

     

    To fix the % Achievement measures I had created, I adjusted the measure as follows:

    Again, the formatting was converted back to text, but I was easily able to fix this as you usually would. 

     

    Can't believe such a simple solution works for this issue. I wanted to make it more complicated than it needed to be. This post helped me get to the correct output. 

     

    Thanks again to those who helped!

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Although you can do conditional custom format strings in Excel, I don't think Power BI can handle that yet.  However, you can do it in a measure (i.e., do your math and then based on the result, return a text value if it is blank or errors).

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This sounds like it could work. I have not done formatting within a measure before - could you explain what that might look like? Let's say I have a measure called Consensus EPS that is pulled from another database. This generally should return a number that needs to be formatted as USD, but if it does return a blank, I want "N/A" to be returned. I have another measure that is similar, except sometimes 'infinity' is returned... I am not sure how that should be formatted in a measure, but I would also like "N/A" to be returned for that.

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Try the following code:

         

            #"Replaced Value" = Table.ReplaceValue(#"Changed Type","","N/A",Replacer.ReplaceValue,{"Value1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","infinity","N/A",Replacer.ReplaceText,{"Value2"})

         

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.