Forum Discussion
Giving Index Number
Hi The most powerful ad helper community 🙂
i need your kind supports to create a column.let me explain what i need pls :
I have table as below , wtih the named columns : "material" , "Order Number " , "Delivery Date " . I would like to give index numbers for each rows. So i wouldl like to create a new column as i marked yellow as below sample table . But every each material will have its own group for index number .Every each material will start from 1 for index .
Rules will be like that :
- System will check delivery date , which row's delivery date is earlier that row will have priority , earliest delivery date row will take index number 1. If the delivery date will be same for some rows , then system will check order number and which order number is smaller this row will have priority.The main check is about delivery time earlier or not , then if there is same delivery date then second check will be on order number.
- As you see in below table Material A,B,C are starting for index number 1 , all each materials have their own index group.
- Also for Material A and for Material B there are rows which are same delivery date .So here system made second check , ad checked the order numbers , which order number is smaller system gave priority to this row for index number .(i marked with red the dates that have this issue)
Source : https://drive.google.com/file/d/1fj8NFM5nnt_frWy1kaRG8gPw79NjX_PU/view?usp=sharing
I hope it is clear dears , also i am sharing with you excel source to make your job easier .
thanks in advance for your kind supports dears
The solution I posted was for measures, not new calculated columns in the data table.
If you want to add these columns to the actual data table, you need:
1) New column concatenating date and order number
Concatenate date and order number = INT(INT('Table'[Delivery Date ]) & 'Table'[Order Number])2) New column for the index:
Index = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[Material] = EARLIER ( 'Table'[Material] ) ), 'Table'[Concatenate date and order number], , ASC )and you get this:
Good point! You can solve it for example by "inflating" the date expression: INT(table[Date]) * 100000000000000 + table[order number]
10 Replies
Hi erhan_79 ,
below you will find a great blog post from Raza Rad, explains exactly how to do what you are trying to accomplish
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
Thanks,
Dear richbenmintz
thanks for your support but i can not use Query side because this table is referenced from a live connection.I need a dax formula to create a column on power bı desktop .your quide is tellinng about query solving.Is ıt possible thet you ca share with me DAX formula ?
Here is one way.
1) Create a measure concatenating date (converted into an integer) and order number, making the result an integer:
sort by = INT ( INT ( MAX ( 'Table'[Delivery Date ] ) ) & [Sum order number] )2) create the index using RANKX on this [sort by] measure:
Index column by Material = RANKX ( FILTER ( ALLEXCEPT ( 'Table', 'Table'[Material] ), NOT ( ISBLANK ( [sort by] ) ) ), [sort by], , ASC )and you get this result
Dear PaulDBrown ;
thank you very much ,
sort measure gave error so i coreccted as below , am i right ?
sort by =INT ( INT ( MAX ( 'Table'[Delivery Date ] ) ) & SUM('Table'[Order Number] ))and after that i tried a create a new columnn but i gor below error ,could you pls checki thik i try to create column but your example is measure , is there any way to create colum for ındex?The solution I posted was for measures, not new calculated columns in the data table.
If you want to add these columns to the actual data table, you need:
1) New column concatenating date and order number
Concatenate date and order number = INT(INT('Table'[Delivery Date ]) & 'Table'[Order Number])2) New column for the index:
Index = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[Material] = EARLIER ( 'Table'[Material] ) ), 'Table'[Concatenate date and order number], , ASC )and you get this:
