Hi all Screenshot of the sample source data:Sanitised sample data in usable format:UserIDDayDiscountedHandsetFreeTrariffPhoneHasOrderedQuantitySale11YYSamsungN0021YNAppleN2456631NNSamsungN00 I have a matrix made as below Screenshot:Sanitised sample data in usable format:UserID123451SamsungSamsung NokiaNokiaSamsung2AppleApplePixel PixelSamsung3SamsungPixelNokia AppleNokia4NokiaNokiaPixel PixelSamsung5PixelAppleApple PixelApple6SamsungPixelApple SamsungNokia7AppleNokiaApple ApplePixel8NokiaApplePixel NokiaNokia9SamsungAppleApple AppleNokia10PixelAppleSamsung AppleApple 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:FromChanged ToIn DayHow many usersSamsungNokia31NokiaSamsung51ApplePixel32PixelSamsung52SamsungPixel22NokiaApple41AppleNokia52NokiaPixel31 The model looks like the below: You can find the file in the link below:Like to pbi file Many thanks in advace.

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 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...

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.

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

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	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.

Need Help

13 Replies

Greg_Deckler
Community Champion
4 years ago
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
  Frequent Visitor
  4 years ago
  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
    Super User
    4 years ago
    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

Forum Discussion

List string values in a matrix depending on different permutation

13 Replies

Related content

Circular dependency

Dax query dependency

Including All Permutations In New Table

dependent slicers

Dependent Filters and Filter Depend on Slicer