Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 🙂
Solved! Go to Solution.
Got this solution from another forum:
Just set the relationships one way from DIM table to FACT table and there is no ambiguity.
Got this solution from another forum:
Just set the relationships one way from DIM table to FACT table and there is no ambiguity.
@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!
@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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |