Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
HI,
I have the next table:
Customer Id - rank* - Order Id - SKU
1234 - 1 - 7894 - SKU 1
1234 - 2 - 7856 - SKU 2
1456 - 1 - 7676 - SKU 1
1456 - 2 - 7906 - SKU 3
1789 - 1 - 7236 - SKU 5
1906 - 1 - 7576 - SKU 2
* rank is based on purchase date.
I want to have a column that will show the first SKU that this customer bought. Like:
Customer Id - rank - Order Id - SKU - FIRST SKU
1234 - 1 - 7894 - SKU 1 - SKU 1
1234 - 2 - 7856 - SKU 2 - SKU 1
1456 - 1 - 7676 - SKU 5 - SKU 5
1456 - 1 - 7676 - SKU 2 - SKU 5
1456 - 2 - 7906 - SKU 3 - SKU 5
1789 - 1 - 7236 - SKU 5 - SKU 5
1906 - 1 - 7576 - SKU 2 - SKU 2
In case if one order (like 7676) have few SKU, any SKU can be choose.
Thanks!!
Solved! Go to Solution.
Hi @juli__sia123412 ,
You could try the calculated column below.
Column = CALCULATE ( MAX ( 'Table1'[SKU] ), FILTER ( 'Table1', 'Table1'[Rank] = MIN ( 'Table1'[Rank] ) && 'Table1'[Customer ID] = EARLIER ( Table1[Customer ID] ) ) )
Here is the output.
Best Regards,
Cherry
Hi @juli__sia123412 ,
You could try the calculated column below.
Column = CALCULATE ( MAX ( 'Table1'[SKU] ), FILTER ( 'Table1', 'Table1'[Rank] = MIN ( 'Table1'[Rank] ) && 'Table1'[Customer ID] = EARLIER ( Table1[Customer ID] ) ) )
Here is the output.
Best Regards,
Cherry
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |