I have a very simple question, which can be easily solved in Excel, but it becomes a big one in PowerBI. The problem is due to NOT quite clean data in our system. We have inventory data for each product. When product is sold out, the inventory should be set to zero, but sometimes, it is blank in our system. We use the inventory data to calculate the inventory level compared to the opening inventory. When the data is blank, instead of show 100% sold out, it just shows as Blank in PowerBI Matrix. I know it is our data issue, but it will take much longer to fix the problem in our system. I am wondering if there is a way (either in Power Query or using DAX) to solve this problem.
As you can see the image below, I wanted to show 100% for Week#2 instead of Blank. Please keep it in mind that I have a lot products and it is impossible to go to Power Query to manually insert the data.
When there is no inventory, does the system shows a a row with a blank value in the data source or it eliminates the rows that doesn’t have any value completely. Right now it seems like there is no value related to Channel 1 in week 2 & week 3 in the data provided.
@MudassirAli Hi, Mudassir, you are right that there is no value in the system for Channel 1 in week2 and wee3. Ideally, when there is no inventory for a product, it should have zero for the week when inventory becomes zero and then Blank in the following weeks. Somehow, in the system, for some products it show zero when inventory is zero, but sometimes it has no value (blank) when inventory is zero. That is the challenge I am facing and my question is if it is possible to use DAX to set inventory to zero when it is blank in the data source?
@JBocher Hi, Joaly, that is great! I thought it had to be done using Power Query, but it looks like the calculated table is a much better solution. I will plug in your solution to my real data.
. Just a hint, you can also create a virtual table instead of a physical table inside a measure.