Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Find and mark items with given composition.

I have a couple of million rows of data is given below.

CUSTOMER_NO     SERVICE
11111111        AB
11111111        BC
11111111        XY
11111111        SA
55555555        AB
55555555        BC
55555555        SA
55555555        HG
77777777        WE
77777777        LK
77777777        AS
77777777        GG
22222222        AB
22222222        BC
22222222        SA
99999999        WE
99999999        AS
99999999        GG

We have decided to mark our customers with who are enjoying a few of our services as Gold Group or Silver Group.

I need to summarise them in below manner.

CUSTOMER_NO SERVICE     **MARKER**
11111111    AB      SILVER +XY
11111111    BC      SILVER +XY
11111111    XY      SILVER +XY
11111111    SA      SILVER +XY
55555555    AB      SILVER +HG
55555555    BC      SILVER +HG
55555555    SA      SILVER +HG
55555555    HG      SILVER +HG
77777777    WE      GOLD + LK
77777777    LK      GOLD + LK
77777777    AS      GOLD + LK
77777777    GG      GOLD + LK
22222222    AB      SILVER
22222222    BC      SILVER
22222222    SA      SILVER
99999999    WE      GOLD
99999999    AS      GOLD
99999999    GG      GOLD

The composition of Silver, Gold etc is as below.

MARKER SERVICE
GOLD    WE
GOLD    AS
GOLD    GG
SILVER  AB
SILVER  BC
SILVER  SA

Due to the sheer size of it, it is difficult for me to do it in Excel, and I'm struggling with the mesh up in PBI.

Can you please help me to pull this off?

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi Anonymous ,

    Please refer to the following steps:

    First create a calculated table to get the distinct value for each customer, please refer to the following DAX:

    Table = var a =  ADDCOLUMNS('Custom',"Marker",IF(LOOKUPVALUE(Service[MARKER],Service[SERVICE],'Custom'[Service]) = BLANK(),'Custom'[Service],LOOKUPVALUE(Service[MARKER],Service[SERVICE],'Custom'[Service])))
return DISTINCT(SELECTCOLUMNS(a,"Cust",'Custom'[CUSTOMER_NO],"mark",[Marker]))

    Then create a calculated table to summarize them:

    Table 2 = SUMMARIZE('Table','Table'[Cust],"marker",CONCATENATEX('Table','Table'[mark],"+"))

    After that create a table to get the result:

    Table 3 = ADDCOLUMNS('Custom',"**Marker**",LOOKUPVALUE('Table 2'[marker],'Table 2'[Cust],'Custom'[CUSTOMER_NO]))

    The result will like below:

    Best Regards,

    Teige

1 Reply

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi Anonymous ,

    Please refer to the following steps:

    First create a calculated table to get the distinct value for each customer, please refer to the following DAX:

    Table = var a =  ADDCOLUMNS('Custom',"Marker",IF(LOOKUPVALUE(Service[MARKER],Service[SERVICE],'Custom'[Service]) = BLANK(),'Custom'[Service],LOOKUPVALUE(Service[MARKER],Service[SERVICE],'Custom'[Service])))
return DISTINCT(SELECTCOLUMNS(a,"Cust",'Custom'[CUSTOMER_NO],"mark",[Marker]))

    Then create a calculated table to summarize them:

    Table 2 = SUMMARIZE('Table','Table'[Cust],"marker",CONCATENATEX('Table','Table'[mark],"+"))

    After that create a table to get the result:

    Table 3 = ADDCOLUMNS('Custom',"**Marker**",LOOKUPVALUE('Table 2'[marker],'Table 2'[Cust],'Custom'[CUSTOMER_NO]))

    The result will like below:

    Best Regards,

    Teige