topic Re: How to display zero using Measure or column value (when there is no value)? in DAX Commands and Tips

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

JustinDoh1 — Wed, 28 Feb 2024 00:33:27 GMT

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?

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

lbendlin — Wed, 28 Feb 2024 14:01:23 GMT

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.

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

JustinDoh1 — Wed, 28 Feb 2024 17:12:12 GMT

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.

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

lbendlin — Wed, 28 Feb 2024 17:44:24 GMT

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

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

JustinDoh1 — Thu, 29 Feb 2024 01:18:20 GMT

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.

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

lbendlin — Thu, 29 Feb 2024 02:13:02 GMT

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.

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

JustinDoh1 — Thu, 29 Feb 2024 05:04:06 GMT

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?

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

JustinDoh1 — Fri, 01 Mar 2024 01:35:12 GMT

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.

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

lbendlin — Fri, 01 Mar 2024 01:04:46 GMT

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.

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

pr1351 — Fri, 01 Mar 2024 01:19:17 GMT

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

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

JustinDoh1 — Fri, 01 Mar 2024 01:48:17 GMT

lbendlin

Ok. Thank you so much for your feedback.

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

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

JustinDoh1 — Sat, 02 Mar 2024 23:49:07 GMT

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.

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

lbendlin — Sun, 03 Mar 2024 00:16:18 GMT

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.

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

JustinDoh1 — Sun, 03 Mar 2024 02:10:05 GMT

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.

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

JustinDoh1 — Mon, 04 Mar 2024 20:17:24 GMT

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.

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

lbendlin — Wed, 06 Mar 2024 01:03:30 GMT

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