How to get date from another table where two tables have relationship with calendar table?

[Anonymous]

Hi. I'm lost on what to do here. I have a table for warehouse inventory and one for orders. The goal is to display a table visual where if there is no inventory for a particular product in the warehouse, see if there is an open order associated with that product and display the date of the order if there is one.

I already pre-filtered out using power query any products with inventory so the warehouse inventory table only has products with no inventory. I have a measure to check if the product has a specific status (status is A and base is true). These are the only products I care about.

When I create this table visual, it works as long as I don't have a relationship between the order table and calendar table. If there is one, the visual does not work. I'm guessing it has to do with both the warehouse inventory table and order table having a relationship with the calendar table. But I do need the order table to have a relationship with the calendar table because I need to create visuals for orders (e.g. bar graph to show how many orders per month).

Here is a sample PBI file where the order table has the relationship to the calendar inactive so the open order dates are displaying properly. If you activate the relationship, it messes up the visual.

https://www.dropbox.com/scl/fi/m76fe0xahzp5slhfwxzs7/OOS.pbix?rlkey=cp2eagy1r46s1u5sxhpmngeni&dl=0

[Anonymous]

That's strange. I see the last date on the calendar table as 9/27/2023. Maybe the file corrupted during the upload?

[lbendlin]

Interesting. I downloaded it again and now it is covering.

Anyway, you say you have issues with the right table? 

This is with the full data model enabled (beautiful data model, by the way!)

[Anonymous]

Thanks! I thought a star schema is pretty standard? 🙂

Yes, the way I have the visual is how I want it to present the data. If the product is in a status of "A" and true for Base, show me the order date if any. If none, show blank. Example: product 99580 has a status of "A" and Base is true. It has an order in the Orders table so it displays 7/3/2023.

The issue is if I set the relationship between the Order and Calendar tables to active, this visual no longer works. The Open Order column is entirely blank. I have a theory it has to do with the single date slicer but even with the date slicer on the full calendar range, it still doesn't work.

I need to have that relationship active in order to build visuals for the orders by itself.

[lbendlin · Accepted solution]

You will have to decide how you want to consume the data model. You can use REMOVEFILTERS on the date to temporarily ignore the joins.

[Anonymous]

Thanks! Putting REMOVEFILTERS on the calendar date to find the order date worked.

CALCULATE(MIN(Orders[Order Date]), REMOVEFILTERS('Calendar'[Date]))

I think I slightly understand now. The table visual already filtered the calendar table to some degree to show inventory so the measure to get the order date was restricted within that context. Therefore, the REMOVEFILTERS has to be used so it can look through all dates.

[lbendlin]

Your calendar table is too short. It ends on 3/23/2023 but your orders don't start until 6/16/2023.