List string values in a matrix depending on different permutation

[TopHat]

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	Samsung	N	0	0
2	1	Y	N	Apple	N	2	4566
3	1	N	N	Samsung	N	0	0

I have a matrix made as below

Screenshot:

Sanitised sample data in usable format:

UserID	1	2	3	4	5
1	Samsung	Samsung	Nokia	Nokia	Samsung
2	Apple	Apple	Pixel	Pixel	Samsung
3	Samsung	Pixel	Nokia	Apple	Nokia
4	Nokia	Nokia	Pixel	Pixel	Samsung
5	Pixel	Apple	Apple	Pixel	Apple
6	Samsung	Pixel	Apple	Samsung	Nokia
7	Apple	Nokia	Apple	Apple	Pixel
8	Nokia	Apple	Pixel	Nokia	Nokia
9	Samsung	Apple	Apple	Apple	Nokia
10	Pixel	Apple	Samsung	Apple	Apple

The Days are across and Rows have UserID and Values are names of Phones:

I need a second table that displays the list of times a phone changed from one day to the next day by each user.

Additionally also need the number of times users have made each kind of change.

The comparison is only against the previous day.

The output I am after is (please note the table below is not complete):

Screenshot:

Sanitised sample data in usable format:

From	Changed To	In Day	How many users
Samsung	Nokia	3	1
Nokia	Samsung	5	1
Apple	Pixel	3	2
Pixel	Samsung	5	2
Samsung	Pixel	2	2
Nokia	Apple	4	1
Apple	Nokia	5	2
Nokia	Pixel	3	1

The model looks like the below:

You can find the file in the link below:

Like to pbi file 

Many thanks in advace.

[Greg_Deckler]

@TopHat I fail to understand your numbers. For the first row in your table you have Samsung to Nokia day 3 and you have 3 users but only one switched on day 3 from Samsung to Nokia. So...

[TopHat]

Apologies I have left the "How many users" column with dummy data but I have now correct it and it now contains real output data.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[TopHat]

Hi @lbendlin , The output that I have posted is the actual output. 

It is not a set of complete rows as there will be more combinations int the fileds "From" and "Changed to" but the first 8 rows contain actual permutations and is the right out come.

[lbendlin]

Please provide sanitized sample data in usable format. A screenshot is not a usable format.

[TopHat]

I see what you mean. 

I have updated the output now hope that's better.

[lbendlin]

Nearly there. Now please do the same for the source data.

[TopHat]

Okay done that too now 😊

[lbendlin]

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.

[lbendlin]

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]

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. 

[lbendlin]

see attached

[TopHat]

Thanks @lbendlin.

I have also added what the source looks like.