Showing results for 
Search instead for 
Did you mean: 
pstanek Member

Products with zero numbers

I have  two tables in direct query list of products and stock.

Relation Stock->Product

I need to calculate list of products that I don´t have in stock.

Problem is that I have written only products that have positive numbers in stock.

Product I don´t have in stock, It can´t be found in my table.  For example

Product database:

Product A  id

Product b  id2

Product c id3  


Stock list

Product B id2 6

Product C id3 1



I don´t have  Product A  id 0 written in stock.




Baskar Super Contributor
Super Contributor

Re: Products with zero numbers

I didnt get u .

can please give us little bit clear.

SamLester Established Member
Established Member

Re: Products with zero numbers

If you are using direct query to return the list of products that only have stock (stock value above 0), you can either create another query to return the products with zero stock or update the current view/query to return all products, then handle the check through a filter or DAX calculation in Power BI Desktop.


If it is a SQL Server backend, your additional query to return out of stock products would be something like:


SELECT P.ProductName 
FROM Products P INNER JOIN Stock S on P.ProductID = S.ProductID
WHERE S.Quantity <= 0


Sam Lester (MSFT)

pstanek Member

Re: Products with zero numbers

I am not sure .IT is MS AZURE



You  have information what you have, but I need to find rest of products. It means what you have not-- in order to buy it .

kcantor Super Contributor
Super Contributor

Re: Products with zero numbers


In all honesty, a quick and simply solution would be to create an if statement column in power query to return a value if stock is available. Then filter the list by that value.

Has stock = if [Qty on Hand] > 0 then 1 else 0

this would return a 1 if the item has stock. Then drop "Has stock" in as a filter and set it for 1 to see items in stock, or 0 to see items with no stock.

If you can provide a sample of data we might be able to provide better assistance.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-sihou-msft

Re: Products with zero numbers



In this scenario, you can use "Merge Queries" to combine those two tables. If there's no match data from second table, it will return null.



Then you can create a calculated column like:




Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 281 members 2,724 guests
Please welcome our newest community members: