Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.