How to show null values when in data table there's no such data

[ievazal]

Hello!

I am working with a report where in the data the values with 0's aren't included in the data table. My problem is that I must portray in graph's also the 0 values.

Here is a much simplified table, where it's not portrayed that France has 0 oranges:

Country	Product	Amount
Germany	Apple	5
France	Apple	3
Spain	Apple	1
Germany	Orange	2
Spain	Orange	1

Is there a way how I can create data (France = 0 oranges) or show within PBI that France (and any other countries) is the outlier that has no values?

[Shaurya]

Hi @ievazal,

In Power Query create a new table and insert the row for 0 values by using Enter Data option.

Append this table to your actual table and you'll get rows for 0 amount as well for specific countries and products.

Now add a table visual that uses Amount column as a filter. Set the filter to display data where value is 0.

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

[ievazal]

Hi @Shaurya !

So the problem with this is that... I will have 4k+ new rows added daily into this table, therefore I won't be able to physically go over everything and check for which countries have the 0 amount and then add those manually..

Would there be any DAX I could possibly use to make this doable?

[Shaurya]

Hi @ievazal,

Well in that case there has to be a list of all the countries and products. Since a 0 value means that there is no row for that combination of Country and Product, there should be a dimension that still has those names listed. In that case we can write formulas that check whether there are countries and products that do not have any value.

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

[ievazal]

Any advice on what formulas I could use? Should I use M code for it? IF statements? I am a beginner, only know the absolute basic DAX, so trying to think about the formula to use for this breaks my brain