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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Post Prodigy
Post Prodigy

How to display zero using Measure or column value (when there is no value)?

I am trying to display two zero (0) value for a line where it has column data and measure values.


I have this bottom measure which does not display anything because there is no value for [Ave Census].




I would like to know whether I need to do something on the  vw_census_trend_MonthToDate_2[Ave Census] first to force to display zero or whether I could just force to add an zero on the measure:
I tried something like this but it did not work:

Currently,  vw_census_trend_MonthToDate_2[Ave Census] is a column data, and there is no value on this column.


I also tried something like this, but no success:

Bottom is illustration of the three Values:



What is best approach/solution?





I would prefer you don't do that. Instead, enable "Show items with no data".




View solution in original post

Frequent Visitor

Hi, You might use  COALESCE function to display a specific value (such as zero) when there is no value. so it can be like: CALCULATE(SELECTEDVALUE(COALESCE([YourOriginalMeasure], 0)))


Thank you for your feedback.

I tried with Coalesce. but I am getting this error: "Parameter is not the correct type" when I hover over the red underlined area.

And also getting this message on the bottom: Column " cannot be found or may not be used in this expression:


This is original measure:


The data format of the measure "Skill Mix % All" is Percentage.



Post Prodigy
Post Prodigy


Thank you so much for your help again! 🙂

I uploaded my concised and data-safe version into my Google drive here .

There are three tabs/parts ( Actual, Variance and Budget) on this PBI file.

What you would see here is that because "Kyle" does not have "Census Data" for Actual, it would not reflect on the other visuals in both Actual and Variance tabs.




Is it possible to show 0s (on the missing lines)?




You need to do a lot of work on your data model before you can think of that





You need to create a Facilities dimension table and then merge it with the trend and occupancy tables. Then you need another PayerType dimension , and then youcan connect your dimensions to your fact without the need for a joinkey table.


Can you please take a look at my updated PBI file here that has two additional dim tables (tblFacility & tblPayerType)? I deleted all relationships from my previous "JoinKey" table.

I am not sure how to connect tblFacility with 3 fact tables.

Actually, I am lost in many ways 🙂

Also, I am not sure why on the "Actual" tab, it shows fewer columns (4 instead of 6) than others.





Merge these tables


Bidirectional 1:1 between two tables usually indicates that this should be a single table.



This is not yet a dimension table. It is missing a primary key.  Figure out what the primary key is and kick out/downstream anything that prevents it from working.



Avoid bidirectional *:* relationships wherever possible. They are expensive and can lead to wrong results.




I have been modifying relationships between dim table (especially "zzvw_occupancy_trend") with three fact tables for last day or so. 

Here is my PBI file.

I am still having a difficulty with using a Filter (Facility Name).

I am not sure whether I need to bring value from dim table or Fact table to be used for filter.

Can you please take a look at the relationship that I created first and give me any feedback where I am wrong?

Appreciated for your help again.




This looks great.  Filters should in the vast majority of scenarios come from the dimension tables.  That filter choice then flows down into the fact tables.  If you filter on a fact column that choice can not (and should not) propagate back up into the dimension.


Thank you so much again!

I updated PBI as much as I can.

Updated PBI look very well designed, I think.

Now, I think I am ready to get any feedback from you regards to how to display zeros on the "Actual" tab for Kyle.

I have updated my PBI file here.




I would prefer you don't do that. Instead, enable "Show items with no data".





Ok. Thank you so much for your feedback.

I will work on it again tomorrow and let you know 🙂



I was working pretty much all day with relationships.

I thought that "joinkey" table would work, but I will definitely restructure my relationship tomorrow based on your suggestions and let you know where I am at.  Thank you so much!!


BTW, what do you mean by "merge"?

Do I have to combine two tables (like "trend" and "occupancy" tables) or you meant connect with Facility dim table with "trend" and "occupancy" tables?

Super User
Super User

The best approach is not to do this. The blanks are there for a reason, and filling them with 0 skews the data.


The second best approach is to use disconnected tables and cross joins.


Thank you so much for your feedback. I think first option is not my option at this moment.

How do we go about doing with the second options (using disconnected tables and cross joins)?


Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to

Please show the expected outcome based on the sample data you provided.

Want faster answers?

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors