0s From Power Query Source Not Appearing in Table Visual

[Anonymous]

Hello,

I'm pulling in Production Quantity data for various Plants that are scheduled to run on a specific Schedule. The granularity of the table visual is at the Material Number level where I'm also calculating an efficiency percentage within the far-right column. The efficiency percentage is measured as... Production Quantity / Ideal Production Quantity. Sometimes a Plant might be "down" for the day or a Production Line might be "down" for the day and when this happens, I want to show "0" for the Production Quantity in the table visual where the efficiency percentage is 0% as well. Effectively, when the data is aggregated up to a higher level of granularity such as at the month level, I want the 0% percent to be accounted for in the overall efficiency percentage at this monthly level.

The Plant Schedule is a dimension which is at the day, plant, production line and shift level. The dimensional attributes in the table visual are the first 7 fields and the rest of the fields are from my fact table to perform the calculations, etc. As you can see in the table visual screenshot, when the Plant didn't produce anything on 2019-06-12, blank values are displayed. However, when nothing is produced for a given day, shift, production line, etc., I want to see "0" for the quantity produced. (FYI...I have a number of "Prod Qty" type of fields in my table visual screenshot as I was trying different methods to get the "0s" to show).  In Power Query Editor, I performed a right outer join between my Fact table and Plant Schedule Dim table and then set the Production Quantity field to 0 if the field shows (null). I can see the "0s" in my Data Model view but it's not visible in my table visual.

Table Visual

Data Model (filtered down to the same records as table visual screenshot but for 6-12-2019 only)

- One can see the "0s" are present.

What am I missing here?

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you may try to add 0 to the target measure.

Here is an example.

You may create a measure as below.

Value - 2 = SUM('Test'[Value]) + 0

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

I discovered the issue in my Data Model and using a column/key from my Fact table for the relationship join. The column/key isn't always populated with a value. This explains why I couldn't see the 0s.

Thanks for your help!