Create matrix visual based on values in two table columns

[Anonymous]

Hi lovely people,

I have a table loaded in Power BI with many columns, of which two are columns with abbreviations of country name. Let's call these columns 'Country A' and 'Country B', and the table containing these columns 'Countries'. For most rows, the country in the 'Country A' column is the same as the country in the 'Country B' column, but for some rows they are different. You can assume that the countries in both columns are consistently named, i.e. France is abbreviated FR and Italy is abbreviated IT is both columns.

I would now like to create a matrix where the columns represent the countries in the Country A column and the rows represent the countries in the Country B column, and each value in the matrix is supposed to be the number of rows with that combination of countries.

For example, for the Countries table:

Row No.	Country A	Country B
1	US	IT
2	FR	FR
3	IT	FR
4	FR	US
5	FR	US

I would like to get the matrix:

Country A	FR	IT	US
Country B
FR	1	1	0
IT	0	0	1
US	2	0	0

because on 1 row country A is equal to US and country B is IT, and on 2 rows country A is equal to FR and country B is equal two US and so on.

The table (loaded from an Excel sheet) is in fact much larger, but I hope that you understand my issue here. I suppose this problem can be solved using DAX in some way, but I am unsure how.

I would be very thankful if anyone could help me out with this. Many thanks in advance!

Kind regards,

Bertan

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First create a dimension table as below:

Table 2 = DISTINCT(UNION(VALUES('Table'[Country A]),VALUES('Table'[Country B])))

Then create a measure as below:

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Country A]=MAX('Table 2'[Country A])))+0

And you will see:

For the related .pbix ,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

The matrix visual is similar to a table. A table supports two dimensions and the data is flat, meaning duplicate values are displayed and not aggregated. A matrix makes it easier to display data meaningfully across multiple dimensions -- it supports a stepped layout. The matrix automatically aggregates the data and enables drill down.

You can create matrix visuals in Power BI Desktop reports and cross-highlight elements within the matrix with other visuals on that report page. For example, you can select rows, columns, and even individual cells and cross-highlight. Also, individual cells and multiple cell selections can be copied and pasted into other applications.