Build a compare matrix

[Syndicate_Admin]

Hi, 

I want to build a Matrix for this Sample Data

Model	Type
Model 1	A45
Model 1	A12
Model 2	A45
Model 2	B23
Model 2	A12
Model 3	A45
Model 3	B23
Model 3	E24

From this Data i want to find out the absolute differences and get a Matrix like this.

	Model 1	Model 2	Model 3
Model 1	0	1	2
Model 2	1	0	2
Model 3	2	2	0

Can someone help me and give me an advice how to come to the solution.

Thanks a lot.

[v-cgao-msft · Accepted solution]

HI @Syndicate_Admin ,

Please check the model.

DimModel;

relationships:

Absolute Difference = 
VAR _COL = CALCULATETABLE(VALUES('Table'[Type]), 'Table'[Model] = MAX('DimModel'[Model]))
VAR _ROW = CALCULATETABLE(VALUES('Table'[Type]), 'Table'[Model] = MAX('Table'[Model]))
VAR _ROW_COUNT = COUNTROWS(_ROW)
VAR _COL_COUNT = COUNTROWS(_COL)
VAR _DIFF_COUNT = COUNTROWS(INTERSECT(_COL,_ROW))
VAR _RESULT = IF(MAX('DimModel'[Model])>=MAX('Table'[Model]),ABS(_COL_COUNT-_DIFF_COUNT),ABS(_ROW_COUNT-_DIFF_COUNT))
RETURN
_RESULT

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[slorin]

Hi,

Try this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc1RMFTSUXI0MVWK1UERMTRCEjHCUAMScTIyRleDossYQ5cxhi6QiKuRiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, Type = _t]),
Join = Table.NestedJoin(Source, {"Type"}, Source, {"Type"}, "Source", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Source", {"Model"}, {"Model.1"}),
Filter = Table.SelectRows(Expand, each [Model.1] <> [Model]),
Pivot = Table.Pivot(Filter, List.Sort(List.Distinct(Filter[Model.1])), "Model.1", "Type", List.Count)
in
Pivot

Stéphane