Forum Discussion
Filtering one fact table with another fact table
Hi,
I have the following star model in the picture with 3 dimension tables and 2 fact tables. The "Fact Inventory" table simply shows level of inventory for a specific warehouse (WH) in a specific week in part no level. The "Fact Purchase Code History" table shows purchase code for a specific part in a specific week. The purchase code for a part can change over the weeks and can take different values. But as an example, in this model I have code 1, 8 and 9. Now what I want to visualize is total GIT (inventory level) for parts in a specific WH in a specific week and want to be able to filter GIT based on purchase code. This basically means that "Fact Purchase Code History" table should be able to filter "Fact Inventory" table. Since direction of relationship is one directional from all dim tables to both fact tables, I cannot make this filtering. Can you help me to solve this without just changing the filtering direction to two-way?
Thank you!
/shamo
2 Replies
- amitchandak
Super User
shamo , do not join fact try like
Calculate(Sum('Fact Inventory'[Value]), filter('Fact Inventory' , 'Fact Inventory'[purchase code] in allselceted('Fact Purchase Code History'[purchase code])))
Also refer https://docs.microsoft.com/en-us/dax/treatas-function
amitchandak, thanks for your reply!
I did not really understand this measure. There is not purchase code column in Fact Inventory table. Can you please clarify a little more?
Thanks,
shamo
