Forum Discussion
Solved
Need Help! Data transformation
Hi all, I have a set of data and want to transform to a new data model in PowerBI. Below is the screenshot of data transformation. There are two difficult parts for me: 1. combine all the cu...
Hi ryan_mayu
AkhilAshok's solution looks really good. If you want to do it with DAX instead, you could create a table based in yours with something like this:
Table = ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZECOLUMNS ( Table1[Shop]; Table1[Month] ); "Customer"; CALCULATE ( CONCATENATEX ( VALUES ( Table1[Customer] ); Table1[Customer]; ", " ) ); "Amount"; CALCULATE ( SUMX ( Table1; Table1[Amount] ) ) ); "Type"; VAR NumCustomers = LEN ( [Customer] ) - LEN ( SUBSTITUTE ( [Customer]; ","; "" ) ) + 1 RETURN SWITCH ( NumCustomers; 1; "Bad"; 2; "Normal"; 3; "Normal"; "Good" ) )Where it is assumed that number of customers >=4 is of type 'Good'
Hi ryan_mayu
AkhilAshok's solution looks really good. If you want to do it with DAX instead, you could create a table based in yours with something like this:
Table =
ADDCOLUMNS (
ADDCOLUMNS (
SUMMARIZECOLUMNS ( Table1[Shop]; Table1[Month] );
"Customer"; CALCULATE (
CONCATENATEX ( VALUES ( Table1[Customer] ); Table1[Customer]; ", " )
);
"Amount"; CALCULATE ( SUMX ( Table1; Table1[Amount] ) )
);
"Type";
VAR NumCustomers =
LEN ( [Customer] ) - LEN ( SUBSTITUTE ( [Customer]; ","; "" ) )
+ 1
RETURN
SWITCH ( NumCustomers; 1; "Bad"; 2; "Normal"; 3; "Normal"; "Good" )
)Where it is assumed that number of customers >=4 is of type 'Good'
