topic Re: Sum and Countif Matrix in DAX Commands and Tips

Sum and Countif Matrix

Anonymous — Fri, 19 Mar 2021 13:01:45 GMT

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"

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

Image 2

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.

Image 3 - Final Table

Can you help me on how to do this on PowerBI?

Re: Sum and Countif Matrix

JW_van_Holst — Fri, 19 Mar 2021 16:31:48 GMT

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

Re: Sum and Countif Matrix

Anonymous — Sun, 21 Mar 2021 12:55:10 GMT

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?

File Example

Re: Sum and Countif Matrix

JW_van_Holst — Mon, 22 Mar 2021 19:13:13 GMT

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