Forum Discussion
Sum and Countif Matrix
https://drive.google.com/file/d/1y9NFJtMHJDSZUIqZFe2urVR71gUo8LZ0/view?usp=sharing
Here you are. This is a solution with viewer rows. The rows of T1 and T2 are converted to lists contaning all the (column) values. Next I have made a query with a Cartesian of the row indexes of the rows (= lists) of T1 and T2. With a List.Intersect and a List.Count the numer of matches are determined.
The last step in Power query is to build a table with the match counts.
Finally the matrix is constructed in DAX.
Good luck!
//JW
see the Pbix in the link:
https://drive.google.com/file/d/16NMktbIBeRWYyCcYaWBr6SKtYfKdH5gi/view?usp=sharing
Here is the outline of the solution:
Unpivot Table1 and Table2
Per table construct 3 columns Line/Column/Value
Cartersian product between T2 and {1..5}
Merge Cartesian with T1
Look where values are equal (then 1 else 0)
Groupby line T2 and Catesian with T1 and aggregate SUM equals ( 0 or 1)
Caterian Groupby with {11..15}
Compare Groupby with {11..15}
Power query steps are ready. Apply model to Power BI and construct the matrix…
… and Bob’s your uncle!
Good luck,
JW van Holst
Heelo JW_van_Holst.
First of all, thank you very much for your help.But, my tables are very large and these actions are not being completed via PowerQuery, I have 3,268,760 rows in table 1 and 2186 rows in table 2, which keeps growing every day (only table 2).
I would like to know if there is any simpler way of obtaining this result, on a small sample base in Excel, which I would carry out, as in the attachment, but I can't import and work my complete base in excel.
Can you help me again?
https://drive.google.com/file/d/1y9NFJtMHJDSZUIqZFe2urVR71gUo8LZ0/view?usp=sharing
Here you are. This is a solution with viewer rows. The rows of T1 and T2 are converted to lists contaning all the (column) values. Next I have made a query with a Cartesian of the row indexes of the rows (= lists) of T1 and T2. With a List.Intersect and a List.Count the numer of matches are determined.
The last step in Power query is to build a table with the match counts.
Finally the matrix is constructed in DAX.
Good luck!
//JW