Connect multiple fact tables with multiple dimension tables

[bininja]

Hi guys,

I have a issue with relationships.

I have multiple fact tables with multiple columns of primary keys.

Then I have multiple dimension tables where each table should be connected to a specific columns in each of the fact tables above with foreign keys.

The connections are working fine, but the issue is that while I am using the dimension tables as slicers, the slicers does not sync with eachother. I tried changing from single to cross relationship but that gives me ambigious error. I want them to sync so that when a slicer is used, the other slicers only show values for the filtered view and not the whole list.

Here is how the connections are set up.

[Anonymous]

Hi Guys, I have a similar problem. Was anyone able to get an answer for this? 

I tried making the multiple fact tables to a flat (single) fact table by doing the merge in the power query. But my data is coming from the data warehouse and each of the fact tables is 7 Million rows+ (because there at aleast 10-12 primary keys common in both the fact tables). Any help would be very good. 

[parry2k]

@bininja you don't need cross filter to both, can you share relationship diagram you have.

[bininja]

@parry2k Thanks for your reply. The diagram is just as I have posted above. One to many relationship for all of them.

[parry2k]

@bininja well then it shoudl work, one thing is not clear, do you have duplicate for each dimension or same dimension table connects to both fact, if it is the later one then everything should work.

[bininja]

@parry2k So there are same dimensions tables connecting to both fact tables not duplicates.

[parry2k]

@bininja i'm surely missing something here, i think it will make sense to send some sample data with dimension (or put together pbix file with sample data) and let me know what it is not working.

[bininja]

@parry2k Thanks for helping me out on this. 

Here is the file: https://filebin.net/f31diioljvh8zl40

As you can see in the file, I have already sliced on one of the slicers. I want the other slicers on the page to sync with the selected value in the slicer and only show values in all slicers which are related to the chosen one. I hope you understand me. 

Let me know if there is something.

[parry2k]

@bininja I now understand what you are trying to do, here is the post which can helpt but not sure what is the use case you need to do this. Hope above link helps

[bininja]

@parry2k Thanks. This looks good. The thing I am not sure about is how this will work since I have multiple fact tables..?

[bininja]

@parry2k I have one dimension table for each dimension. Each dimension table is connected to the facttables.

I earlier tried using one table as dimension table but I was then only able to makw the relationship wirh fact table on one column and that didnt work as I wanted it to.