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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Virtual_Ames
Employee
Employee

How to deal with a "(BLANK)" value in a card visualization

My dashboard showed "(BLANK)" as the value in a card visualization - when I really wanted to see "0".

 

Is there a best practice to deal with this, or a simple way to adjust this?  

 

Here is my current solution.

 

Note: This does not seem to be a data type issue. The card is calling on a measure that is calculated as follows:

 

          Total CRs that have moved to P = CALCULATE([Total CRs - for calculations], 'Release_CRs_Systems (CRs Moved Only)'[Friendly System Name] = "P")

 

2) The data type for "Total CRs - for caculations" is "Whole Number" 

3) [Total CRs - for calculations] is a SUM function - type "Whole Number"

 

CURRENT SOLUTION:

 

Create a new measure that evaluates [Total CRs that have moved to P] and uses IF(ISBLANK)

    

     DISPLAY CALC FOR CR IN P =

        IF( ISBLANK('Release_CRs_Systems (CRs Moved Only)'[Total CRs that have moved to P])
        , 0
        , ( 'Release_CRs_Systems (CRs Moved Only)'[Total CRs that have moved to P]))

 

***** 

 

Can anyone provide a more elegant solution?

45 REPLIES 45

Here is a more popular one. There are many more ideas posted that are similar.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10110324-count-should-return-0-in...

Anonymous
Not applicable

Im quite new to power bi. can you please help me here ?

 

I have a card which counts total values in column A. if if select specific values in slicer sometimes it comes up as (blank) instead of zero.

 

if i need to create Measure = expression + 0 - what should be under expression ? measure = sum(table[column A]) + 0 ? and how to apply it to the card when the measure is created ?

 

thank you

Anonymous
Not applicable

Hi @Anonymous ,

 

Right click on the left side pane on any Field name and Click "Add New Measure".

Give a name to the measure. Ex: MsColumnA

Assign your expression and " + 0 " at the end.

Ex:

MsColumnA = Sum(Table_Name[columnA]) + 0

 

Now your new measure will appear on the left side pane. 

Drag and drop it to the Card Field.

 

Renaud
Advocate IV
Advocate IV

Hello,

 

inspired by this article explaining the blank value:

https://www.sqlbi.com/articles/blank-handling-in-dax/

 

i simply added a +0 after my expression and it works, since <blank> + 0 = 0.

Instead of Measure = expression,

i simply write it: Measure = expression + 0

 

this avoids repeating twice the expression in the measure definition

Had the same problem. Used this suggestion and it works perfectly. 

 

Simple and elegant!

This one is really superb!

Super clever!! tnx

Sean
Community Champion
Community Champion

@Virtual_Ames I deal with this the same way... IF ( ISBLANK...

Sean
Community Champion
Community Champion

@Virtual_Ames I was wondering the same a little over a month ago - got only one response - look here

 

http://community.powerbi.com/t5/Desktop/Blank-in-Card-Multi-row-Card/m-p/21946

Thanks - that is a bit easier, and appreciated. 

 

Still hoping for an easier way - if not, perhaps this would be a feature request as this seems to imply that for every card where I do not want to see "(BLANK)" when certain filters are applied - I need to create a new measure and apply the IF

Hi @Virtual_Ames,

 

As sugegsted by @Sean and @MattAllington, we need to use =IF(ISBLANK...) to force blank values as 0. Or you can replace all blank values as 0 when you retrieve data from source table. Currently, the Card visualization will not trace blank value as 0.

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Virtual_Ames
Employee
Employee

I had an issue with a dashboard showing "(BLANK)" as the value in a card visualization - when I really wanted to see "0".

 

If someone has a more elegant solution - I'd like to hear it - but here is my current solution.

 

PROBLEM:

 

     Card Visualization shows "(BLANK)" when a numerical value is desired.

 

 

 

 

Blank normally indicates the absence of any records that match the result.  I guess you could just write an if statement like this

 

New Measure = if(ISBLANK([Original Measure]),0,[Original Measure])



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

@MattAllington 

 

In addition to the +0 trick, I have also used the following to get just white space on some visuals like the Card - that can be effective as I find zero to be misleading at times and (Blank) is distracting.

 

Measure = IF( ISBLANK([Measure]), "", [Measure]) -- note the "" is two quotation marks together. Of course you can put space between the marks if you are creating a concatenated string.

 

Interestingly I have been working with the Call Out Value on the Gauge today. The usual

DIVIDE( x, y, BLANK()) gives a '(Blank)' visual but so does DIVIDE( x, y, "") - or any string. So there's some inconsistency there. DIVIDE(x, y, 0) gives '0%' - so there's nothing that returns literally nothing. My users will just have to suffer with (Blank)!

 

 

 

Hi,

 

Displaying 0(zero) in place of (BLANK) gives out wrong information to the user. Instead can we have 'N/A' or 'No Data' displayed instead of 0?

 

I tried to use, New Measure = if(ISBLANK([Original Measure]),0,[Original Measure])

and replace 0 with N/A like,

 New Measure = if(ISBLANK([Original Measure]),'N/A',[Original Measure]).

 

I am able to now see 'N/A' when the data is not present, but if there is data my [Original Measure] is converted to decimal number(Eg:22.00) which I actually want to see as whole number (22).

 

None of the functions like INT,ROUNDDOWN is able to truncate the decimal number to whole number.

 

Any ideas/suggestions would be helpful.

 

Thanks,

Nayana

Hello NayanaBhagat,

You can truncate decimal value  without using any dax/function.

Go to Modeling tab
2. Just below Format :  you can decrease decimal places . Make it 0 and you will have only whole number.

1111.PNG

Hi All,

I'm facing the same issue, where i want the card viusal to show '0' instead of 'Blank', but my problem is the column i used is never empty, but after applying few conditions it turns out to be blank. So  both  '+0' or 'IF' conditons are not helping me!!! 

Another trick is to use the advanced card visual with conditional formatting. You can then set the font color to white for when it equals 0.

Replacing blank with 0(zero) will send out a wrong inference to the user implying that the value is zero. Instead is there an alternate way to display 'N/A' or 'No Data' instead of (Blank). 

 

I tried the option that you suggested by replacing '0' with 'N/A'.

New Measure = if(ISBLANK([Original Measure]),'N/A',[Original Measure])

 

However, the Original Measure converts to a decimal digit (Eg: 22.00) which I actually want to show as a whole number(22). No function like INT,ROUNDTODOWN seem to be truncating the decimal part.

Any ideas would be appreciated.

 

Thanks,

Nayana

I'm trying this method with a DISTINCTCOUNT and I'm still getting blanks.

 

Vendor Count:=IF(ISBLANK(DISTINCTCOUNT([Vendor Sourcing HierarchyVendor Sourcing HierarchyVendorid])),0,DISTINCTCOUNT([Vendor Sourcing HierarchyVendor Sourcing HierarchyVendorid]))

 

 

I have a list of stores in my rows, and time period as columns. Some periods, don't have data, some do.  The ones that don't have any data (No Values) show up as blanks.  I want to seed the blanks with 0's

 

Thoughts/Help?


@MattAllington wrote:

Blank normally indicates the absence of any records that match the result.  I guess you could just write an if statement like this

 

New Measure = if(ISBLANK([Original Measure]),0,[Original Measure])


 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.