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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
JustinDoh1
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].

 

JustinDoh1_0-1709079762935.png

 

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:
JustinDoh1_1-1709079807287.png

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:

JustinDoh1_2-1709080099835.png
 
Bottom is illustration of the three Values:
JustinDoh1_3-1709080289266.png

 

 

What is best approach/solution?

 

 

 

1 ACCEPTED SOLUTION

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

 

lbendlin_0-1709686992015.png

 

View solution in original post

15 REPLIES 15
pr1351
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)))

@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:

JustinDoh1_0-1709431425642.png

This is original measure:

JustinDoh1_1-1709431534070.png

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

 

 

JustinDoh1
Post Prodigy
Post Prodigy

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

JustinDoh1_1-1709169477888.png

 

 

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

 

Thanks.

 

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

 

lbendlin_0-1709172623736.png

 

 

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.

@lbendlin 

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.

Appreciated.

JustinDoh1_1-1709255433816.png

 

 

Merge these tables

lbendlin_0-1709254896881.png

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

 

lbendlin_1-1709254945395.png

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.

 

lbendlin_2-1709255046561.png

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

 

 

@lbendlin 

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.

JustinDoh1_0-1709423337618.png

 

 

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.

@lbendlin 

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.

JustinDoh1_2-1709583391803.png

 

 

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

 

lbendlin_0-1709686992015.png

 

@lbendlin 

Ok. Thank you so much for your feedback.

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

 

@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?

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

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

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

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/1447...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.