Forum Discussion
List string values in a matrix depending on different permutation
See attached for one possible solution. Note that your data model is (mostly) accurate but rather useless for the question you are trying to answer. Instead you need to use tons of disconnected tables and crossjoins. Might get away with using TREATAS or CROSSJOIN(,,none) - which might be necessary if you want this to scale for larger date ranges.
Slightly "optimized" version stuffing everything into a single calculated table.
1. source data "Facts"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC0uzUtHsBSATL/87MxEJBqmKFYnWskIyHcsKMhJRaIDMitScxRgDDQNxii2wFSAjVZAMgNiF0iDCRYX4LXBFEkcxWkKGBIg5WZYHQTXgJBCOMkcw6FIGlBCAazcAl0Zsg9QvQZSboliK4YPMEPI0ACLj5HiDzV+YmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"UserID"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Day"}, {"Value", "Phone"}})
in
#"Renamed Columns"2. Calculated Table "Summary"
Summary = FILTER(CROSSJOIN(DISTINCT(SELECTCOLUMNS(Facts,"From Phone",Facts[Phone])),DISTINCT(SELECTCOLUMNS(Facts,"To Phone",Facts[Phone])),VALUES(Facts[Day])),[From Phone]<>[To Phone] && Facts[Day]>1)3. adding calculated column:
Users =
var d=Summary[Day]
var f=Summary[From Phone]
var t=Summary[To Phone]
var u=SELECTCOLUMNS(CALCULATETABLE(Facts,Facts[Phone]=t,Facts[Day]=d),"User",Facts[UserID])
var p=SELECTCOLUMNS(CALCULATETABLE(Facts,Facts[Phone]=f,Facts[Day]=d-1),"User",Facts[UserID])
return COUNTROWS(intersect(u,p))Result:
see attached
Thanks for taking the time to look into this but you have used the result of the first matrix as the source data rather than what is in the file.
Also, would you mind sharing the file for the slightly optimised one as well please.
