Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |