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 ?

Answer 1

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).

Answer 2

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

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

1. 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], "," )

2. UnSelect Column 'Letter2' and select 'measure' Column

3. Change Visual table to 'Matrix' Type

4. Change matrix Properties Row and Column SubToal to Off

5. Your Expected Result is ready

You can actual visual check step Here

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