PowerBI Matrix with "All" Cell data?

Question

In How can I create a relationship matrix in Excel I asked for a way to create a relationship matrix in Excel - copied here for convenience:

1 A X
1 A Y
1 B X             A   B   C
1 B Z         1  X,Y X,Z
2 A Z   ==>   2   Z   X   Y
2 B X         3   Y   Z
3 A Y
3 A Z
2 C Y

Currently I am evaluating O365 PowerBI for the same feature. There is a matrix visualisation that looks promising, but for cell data one can not choose "all" or a "concat"-operation like TEXTJOIN. It has only "First" or "Last".

Any way to get that relationship matrix feature in PowerBI ?

score 3 · Accepted Answer · answered Oct 11 '19 at 17:08

3

There are analogous concatenation functions in DAX as well as M. I'll give a DAX solution here since it allows more dynamic interaction and is quite simple.

Assuming your the columns of Table1 are Number, Letter1, and Letter2 and you have Number on rows and Letter1 on columns, you can use this measure to get the matrix you're after:

All = CONCATENATEX ( VALUES ( Table1[Letter2] ), Table1[Letter2], "," )

The VALUES function returns a list of all distinct values of the column argument that are within the current filter context. The CONCATENATEX function then iterates through this list/table (1st argument) and for each item concatenates the value/expression Table1[Letter2] (2nd argument) separating each one with , (3rd argument).

answered Oct 11 '19 at 17:08

Alexis Olson

38,724
7
42
64

Still no access to Power BI Desktop yet. What is "DAX" and "M"? – Bastl Oct 14 '19 at 17:07
DAX is the language used to write measures (like in my answer). M is the language used in the query editor when importing data. – Alexis Olson Oct 14 '19 at 17:09
1

Right-click on the table and choose "New measure". – Alexis Olson Oct 15 '19 at 13:46

Mr. Bhosale · Answer 2 · 2019-10-17T09:21:55.753

1

Yes Matrix Feature is available.Expected Result using PowerBI is here :

Steps Are : Duplicate your table if you want to compare result.

Add New Measure

Measure = CONCATENATEX(Table1,(Table1[Letter2]),", ")

If you want Disitinct Total then use @Alexis answer code.

All = CONCATENATEX ( VALUES ( Table1[Letter2] ), Table1[Letter2], "," )

UnSelect Column 'Letter2' and select 'measure' Column
Change Visual table to 'Matrix' Type
Change matrix Properties Row and Column SubToal to Off
Your Expected Result is ready

You can actual visual check step Here

Thank you. let me know if you have any concern.

edited Oct 17 '19 at 09:21

answered Oct 16 '19 at 11:01

Mr. Bhosale

3,018
1
17
34

Note that this version of the measure may produce duplicates of letters as seen in the animation when the total rows/columns are included. – Alexis Olson Oct 16 '19 at 19:39
total rows/columns is not requirement in question. Its matches with there expected output. – Mr. Bhosale Oct 17 '19 at 09:14
I just wanted to point out the potential difference. I don't actually know which version OP would prefer. – Alexis Olson Oct 17 '19 at 13:49
@Mr. Bhosale I like your explanation and style very much, but as Alexis was faster I accepted his answer. Sadly I couldn't split the bounty. Thanks, the gif is very helpful! – Bastl Oct 18 '19 at 17:26

PowerBI Matrix with "All" Cell data?

2 Answers2