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".

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

15 Replies

lbendlin
Super User
2 years ago
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.
- JustinDoh1
  Post Prodigy
  2 years ago
  lbendlin
  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)?
  Thanks.
  - lbendlin
    Super User
    2 years ago
    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 https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    
    Please show the expected outcome based on the sample data you provided.
    
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
pr1351
Frequent Visitor
2 years ago
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)))
- JustinDoh1
  Post Prodigy
  2 years ago
  pr1351
  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.
JustinDoh1
Post Prodigy
2 years ago
lbendlin
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)?

Thanks.
- lbendlin
  Super User
  2 years ago
  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.
  - JustinDoh1
    Post Prodigy
    2 years ago
    lbendlin
    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?