Forum Discussion

TopHat's avatar
TopHat
Frequent Visitor
4 years ago

List string values in a matrix depending on different permutation

Hi all   Screenshot of the sample source data: Sanitised sample data in usable format: UserID Day DiscountedHandset FreeTrariff Phone HasOrdered Quantity Sale 1 1 Y Y Samsu...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

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:

 

 

 

TopHat's avatar
TopHat
Frequent Visitor
4 years ago

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.