Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

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:

Snipaste_2019-04-16_16-23-27.png

Best Regards,

Teige

View solution in original post

1 REPLY 1
TeigeGao
Solution Sage
Solution Sage

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:

Snipaste_2019-04-16_16-23-27.png

Best Regards,

Teige

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.