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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ievazal
Frequent Visitor

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

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:

 

CountryProductAmount
GermanyApple5
FranceApple3
SpainApple1
GermanyOrange2
SpainOrange1

 

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?

4 REPLIES 4
Shaurya
Memorable Member
Memorable Member

Hi @ievazal,

 

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

 

Screenshot 2022-10-11 200248.jpg

 

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

 

Screenshot 2022-10-11 200329.jpg

 

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

 

Screenshot 2022-10-11 200650.jpg

 

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

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?

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 

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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