Forum Discussion

Super User

7 years ago

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

Need Help

AlB

7 years ago

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'

ryan_mayu

Super User

7 years ago

AkhilAshok

Thanks for your reply. Although someone has provided the solution by using DAX in this subject, i still like to learn your solution.

I tried your solution again today. Even if I chose count distinct for customer. I still got duplciated customer names after I expand the column. The results of count and distinct count are the same.

Any thoughts on that? Appreciate your help. Thanks in advance