Forum Discussion
Sum and Countif Matrix
Hi!
The first action I have in my table, is to check the number of values in Table 1 - Line 1 are equal within the Table 2, I am using the formula =SUM (COUNTIF (B12:P12; B3:P3)), as it is in the link "Image 1"
After this process, I need to count how many times we had 11, 12, 13, 14 and 15 equal values, for this process I use the formula =COUNTIF (R12: R25;"=11") in a new column in Table 1
In summary, I need to individually check the rows/cells of table 1 in table 2 and count the number of times the result is 11, 12, 13, 14 and 15, with the final result, 5 additional columns in table 1 with these values comparing against all the rows in table 2.
Can you help me on how to do this on PowerBI?
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
3 Replies
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