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
PeteSmith6730
Frequent Visitor

Help With relationships - trying to create relationships to filter on multiple columns

 

 

I have a tricky relationship issue whilst writing a financial reporting package. See picture at end.
1. MAFact table has amongst other stuff a Conskey column which denotes a specific line in MA reports. Many to one relationship with MAReports - all fine.
2. MA reports has various report definitions in it all with a number of rows and unique conskey;
e.g. Report Dept 1 - Range of conskeys for each line on the report;

Report Dept 2 - range of different conskeys ro each line etc, etc.
3. MA Fact table has another column called cost centre with 2 dimensions attached for operation codes and site codes.
PBI is running fantastically well with slicers on Date, bud/act streams, opcodes and sitecodes.

However I need to derive a definitive report list by manager. Manager 1 may need the reports from Dept1 for site A; Manager 2 from Dept 3 for sites 5 and 6 etc. I have written another dimension table - manager distribution and can link this to MAReports by report name. But this dimension table will also require relationships with the operation code and site code table. And I cannot get this to work at all.
The only work around i can see is to merge data in fact table to generate a column with the concatenation of cost centre and manager, but this will make the fact table huge, will probably disturb the numbers/measures that are already working nicely and I feel there must be a bettter way that I can't see. Could anyone out there assist??

Pete
ReportModel.png

1 REPLY 1
v-lili6-msft
Community Support
Community Support

hi, @PeteSmith6730 

"But this dimension table will also require relationships with the operation code and site code table."

Why do you want to create the relationship between dim tables? It is not necessary to do it, just keep the cross filter direction is "Both", then it should work well.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.