cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amconnel
Resolver II
Resolver II

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!

1 ACCEPTED SOLUTION

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: 

amconnel_0-1622050178837.png

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:

amconnel_1-1622050309918.png

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!

View solution in original post

6 REPLIES 6
amconnel
Resolver II
Resolver II

Test reply

mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Hi @amconnel ,

 

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"})

 image.pngimage.png

 

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.

I think this is along the right track. Allow me to be more specific than before, so hopefully we can nail this down. I have two columns (Bloomberg EPS and Bloomberg Revenue) that have null values that need to be returned as "N/A" in the visual. See the following photos. 

Null Example.png                       Null-Number Example.png

On the top is a company that does not have data for these measures, so all values are null. On the bottom is a company that does have data for these measures. When this measure is selected for a visual with the correct period, company, and source, these values (2720.714 and 0.585) will be returned. When the first company is selected, blank values are shown. See the image below. 

Current Output.png

Next (which you can also view in the photo directly above), there are two measures (% Achievement - EPS and % Achievement - Revenue; both show as % Achievement in the visiual above) that have been calculated in Power BI as a new measure, not Power Query. So, I will not be able to edit this in Power Query. They are calculated using the two columns previously mentioned as well as general EPS and Revenue, respectively. Please see the example below of how % Achievement - EPS is calculated.

Infinity Example.png

These two % Achievement measures return infinity as you can see in the matrix shown above. I believe once Bloomberg EPS and Bloomberg Revenue columns are fixed to return N/A when a value is not present, this column would also return N/A... Do you think that would be correct? If not, I may need some help figuring out how to recalculate these two % Achievement columns in Power Query rather than Power BI. 

 

Sorry for all of the trouble with this - I really appreciate your help thus far!

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: 

amconnel_0-1622050178837.png

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:

amconnel_1-1622050309918.png

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!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors