Forum Discussion
Matrix table with multiple columns as rows and measures doing calculations
Hi Nobuko42
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank parry2k and Ashish_Mathur , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum.
We’ve used a combination of a clean data model and a DAX measure to produce the correct Construct-by-Region results. By separating customers, costs, and constructs into proper tables, the report now calculates each construct’s total cost accurately, even when customers have multiple constructs.
Please refer to the attached .pbix file for a working example of the full solution.
I hope the information provided helps you to fix the issue.If it is still outstanding, please share any additional information so we can assist you further.
Best Regards,
Community Support Team
Nobuko42, the shape of your table is not as per the best practices. As you mentioned, you cannot unpivot, but that is the best approach; otherwise, you will be noodling around the solution. Even though you have mentioned it will break the relationship, you have to think long-term and plan for the data model, which is scalable and doesn't require a lot of work to achieve a simple goal.
I hope it helps.
What could be done then if the table is unpovited resulting in a many to many relationship? The data model structure consists of a cost table and customer records table. A customer typically has multiple costs associated to them in a given period, thus there is a one to many relationship between the two tables using a customer ID. If I were to unpovit the customer table, is there a way to address the many to many relationship created?
Hi,
Selete the Many to Many relationship. Create a table with unique customer ID's. Create a Many to One relationship from the fact tables to this unique customer table.
Hi Ashish,
Thank you for your response. What you are referring to is I believe a bridge table. However I don't believe that will help resolve the problem I have.
If table A is the unpivoted table, with duplicates of the customer ID and the constructs, table B is the cost table, and table C is the bridging table with a unique list of customer IDs, the one to many relationships will be on the customer IDs. If I use the unpovited table to list out the constructs, I don't see how the cost table would then know to associate each cost with construct, unless I were to use a cross filter direction relationship that goes both ways between the unpivot table and the bridge table.
That would then bring the concern of using a two directional filter relationship, which I am not very familiar with but have read should typically be avoided due to potential undesired results.Below are examples of the tables. Table A has customer information that associates only to the customer, and not directly related to the cost table (noting this in case it is suggested to move the construct column to the cost table).
Table A:
Customer ID Construct 1001 Construct 1 1002 Construct 2 1002 Construct 4 1003 Construct 1 1003 Construct 3 1004 Construct 2 1004 Construct 3 1004 Construct 4 1005 Construct 2 1005 Construct 3 1006 Construct 3 1006 Construct 4 1007 Construct 4 1008 Construct 1 1008 Construct 4 Table B:
Customer ID Cost Cost Code 1001 100 A 1001 900 B 1001 500 C 1002 600 D 1002 200 E 1003 100 A 1003 500 C 1004 100 A 1004 600 D 1004 900 B 1005 600 D 1005 500 C 1006 900 B 1006 600 D 1007 500 C 1008 900 B 1008 500 C Table C:
Customer ID 1001 1002 1003 1004 1005 1006 1007 1008 Hi,
Based on the 2 tables shared by you, show the expected result.
