cancel
Showing results for 
Search instead for 
Did you mean: 
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
Community Support
Community Support

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

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors