Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |