Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Thanks,
Sam Lester (MSFT)
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 .
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:
IF(Table[column]=BLANK(),0,Table[column])
Regards,
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.
Proud to be a Super User!
I didnt get u .
can please give us little bit clear.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |