Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering multiple Fields over multiple Tables

Hello community!
I have a problem, which is so specific that I didn't even know what to search for.
I have two tables which I am using in one page of my report, both tables have a column "Project" which refers to the same projects (and contains the same values) and I want to filter by that project using just one slicer. My research has led me so far as to make a new dimensions table for the projects (which only contains one column) and connect it to both fact tables, but I have done the same for the reporting period, where i need the dimension table to resolve the id into quarter and year. So I have both fact tables connected 1:n to the periods dimension table and cannot connect them to the new project dimension table, because it would lead to an ambiguity between the connections.
example.jpg

The two tables can not be combined, as their data is very different, where each row of one table contains the whole information for one quarter and the other table has multiple rows for each quarter with key-value pairs for a column chart.

Is there any way to fix this?

 

Many thanks in advance 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Got this solution from another forum:
Just set the relationships one way from DIM table to FACT table and there is no ambiguity.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Got this solution from another forum:
Just set the relationships one way from DIM table to FACT table and there is no ambiguity.

Anonymous
Not applicable

@amitchandak 
I updated the post to include a structural scheme and point out that the fact tables contain wildly different data.
I see no way around having to use these tables and relating them somehow.

Thank you for your help!

amitchandak
Super User
Super User

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

For project you can create a table like 

project = distinct(union(distinct(Table1[project]),distinct(Table2[project])))

 

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

 

Join with both tables. You can do the same in power query too.

You can create date from month, qtr, or year and join with the date table https://www.youtube.com/watch?v=yPQ9UV37LOU

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.