Forum Discussion
Build a compare matrix
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.
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 _RESULTBest Regards,
Gao
Community Support TeamIf 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
2 Replies
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
PivotStéphane
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 _RESULTBest Regards,
Gao
Community Support TeamIf 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
