How to index column on multiple values

[Anonymous]

This is the table I have

PurchaseOrderNo	ItemCode	ItemCodeDesc	QuantityOrdered	QuantityReceived	QuantityBackordered	QuantityInvoiced	RequiredDate	PODATE	Received
0096942	05-060	SET SPRING,  .177 DIA 17-7 STA	25	25	0	25	7/15/2020	3/20/2020 0:00	R
0096942	05-060	SET SPRING,  .177 DIA 17-7 STA	525	525	0	525	9/1/2020	3/20/2020 0:00	R
0096942	05-060	SET SPRING,  .177 DIA 17-7 STA	500	500	0	500	8/3/2020	3/20/2020 0:00	R
0099002	05-060	SET SPRING,  .177 DIA 17-7 STA	200	200	0	28	5/24/2021	2/4/2021 0:00	R
0099002	05-060	SET SPRING,  .177 DIA 17-7 STA	200	200	0	200	5/24/2021	2/4/2021 0:00	R
0099002	05-060	SET SPRING,  .177 DIA 17-7 STA	200	200	0	200	5/24/2021	2/4/2021 0:00	R
0099749	05-060	SET SPRING,  .177 DIA 17-7 STA	350	350	0	350	6/15/2021	4/29/2021 0:00	R
0099749	05-060	SET SPRING,  .177 DIA 17-7 STA	305	305	0	305	7/15/2021	4/29/2021 0:00	R
0100522	05-060	SET SPRING,  .177 DIA 17-7 STA	350	350	0	350	9/29/2021	6/29/2021 0:00	R
0100522	05-060	SET SPRING,  .177 DIA 17-7 STA	350	350	0	350	9/29/2021	6/29/2021 0:00	R
0101841	05-060	SET SPRING,  .177 DIA 17-7 STA	400	400	0	400	5/4/2022	10/27/2021 0:00	R
0101841	05-060	SET SPRING,  .177 DIA 17-7 STA	400	400	0	400	5/4/2022	10/27/2021 0:00	R
0102776	05-060	SET SPRING,  .177 DIA 17-7 STA	300	300	0	300	4/26/2022	12/29/2021 0:00	R
0102776	05-060	SET SPRING,  .177 DIA 17-7 STA	300	300	0	300	4/26/2022	12/29/2021 0:00	R
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	250	4/15/2022	2/28/2022 0:00	R
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	250	6/17/2022	2/28/2022 0:00	R
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	0	8/1/2022	2/28/2022 0:00	R
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	500	7/11/2022	2/28/2022 0:00	R
0105254	05-060	SET SPRING,  .177 DIA 17-7 STA	550	550	0	550	11/30/2022	8/8/2022 0:00	R
0105254	05-060	SET SPRING,  .177 DIA 17-7 STA	550	550	0	550	11/30/2022	8/8/2022 0:00	R
0106527	05-060	SET SPRING,  .177 DIA 17-7 STA	500	0	0	0	2/28/2023	1/4/2023 0:00
0106527	05-060	SET SPRING,  .177 DIA 17-7 STA	250	0	0	0	6/30/2023	1/4/2023 0:00
0106527	05-060	SET SPRING,  .177 DIA 17-7 STA	250	0	0	0	9/1/2023	1/4/2023 0:00

I need to add an index that will index by the Requireddate and PO Number itemcode. So I would expect my results to look like this: 

PurchaseOrderNo	ItemCode	ItemCodeDesc	QuantityOrdered	QuantityReceived	QuantityBackordered	QuantityInvoiced	RequiredDate	PODATE	Received	Index
0096942	05-060	SET SPRING,  .177 DIA 17-7 STA	25	25	0	25	7/15/2020	3/20/2020 0:00	R	1
0096942	05-060	SET SPRING,  .177 DIA 17-7 STA	525	525	0	525	9/1/2020	3/20/2020 0:00	R	2
0096942	05-060	SET SPRING,  .177 DIA 17-7 STA	500	500	0	500	8/3/2020	3/20/2020 0:00	R	3
0099002	05-060	SET SPRING,  .177 DIA 17-7 STA	200	200	0	28	5/24/2021	2/4/2021 0:00	R	1
0099002	05-060	SET SPRING,  .177 DIA 17-7 STA	200	200	0	200	5/24/2021	2/4/2021 0:00	R	2
0099002	05-060	SET SPRING,  .177 DIA 17-7 STA	200	200	0	200	5/24/2021	2/4/2021 0:00	R	3
0099749	05-060	SET SPRING,  .177 DIA 17-7 STA	350	350	0	350	6/15/2021	4/29/2021 0:00	R	1
0099749	05-060	SET SPRING,  .177 DIA 17-7 STA	305	305	0	305	7/15/2021	4/29/2021 0:00	R	2
0100522	05-060	SET SPRING,  .177 DIA 17-7 STA	350	350	0	350	9/29/2021	6/29/2021 0:00	R	1
0100522	05-060	SET SPRING,  .177 DIA 17-7 STA	350	350	0	350	9/29/2021	6/29/2021 0:00	R	2
0101841	05-060	SET SPRING,  .177 DIA 17-7 STA	400	400	0	400	5/4/2022	10/27/2021 0:00	R	1
0101841	05-060	SET SPRING,  .177 DIA 17-7 STA	400	400	0	400	5/4/2022	10/27/2021 0:00	R	2
0102776	05-060	SET SPRING,  .177 DIA 17-7 STA	300	300	0	300	4/26/2022	12/29/2021 0:00	R	1
0102776	05-060	SET SPRING,  .177 DIA 17-7 STA	300	300	0	300	4/26/2022	12/29/2021 0:00	R	2
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	250	4/15/2022	2/28/2022 0:00	R	1
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	250	6/17/2022	2/28/2022 0:00	R	2
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	0	8/1/2022	2/28/2022 0:00	R	3
0103509	05-060	SET SPRING,  .177 DIA 17-7 STA	250	250	0	500	7/11/2022	2/28/2022 0:00	R	4
0105254	05-060	SET SPRING,  .177 DIA 17-7 STA	550	550	0	550	11/30/2022	8/8/2022 0:00	R	1
0105254	05-060	SET SPRING,  .177 DIA 17-7 STA	550	550	0	550	11/30/2022	8/8/2022 0:00	R	2
0106527	05-060	SET SPRING,  .177 DIA 17-7 STA	500	0	0	0	2/28/2023	1/4/2023 0:00		1
0106527	05-060	SET SPRING,  .177 DIA 17-7 STA	250	0	0	0	6/30/2023	1/4/2023 0:00		2
0106527	05-060	SET SPRING,  .177 DIA 17-7 STA	250	0	0	0	9/1/2023	1/4/2023 0:00		3

 Is this possible to do?

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to "add an index that will index by the Requireddate and PO Number itemcode".

For your data , in the [RequiredDate] has the same date , if in the normal rankx logic,it will returns 1 :

If this meet your need , you just need to click "New Column" and enter this:

Column = RANKX( FILTER('Table','Table'[PurchaseOrderNo]=EARLIER('Table'[PurchaseOrderNo]) && 'Table'[ItemCode] = EARLIER('Table'[ItemCode])) , [RequiredDate] ,,ASC,Dense)

If you need to judge the row number for your data , you need to add an index column in Power Query Editor:

Then you can click "New Column" and enter:

Column 2 = RANKX( FILTER('Table','Table'[PurchaseOrderNo]=EARLIER('Table'[PurchaseOrderNo]) && 'Table'[ItemCode] = EARLIER('Table'[ItemCode])) , [RequiredDate]+[Index] ,,ASC,Dense)

Then we can get the result:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly