Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
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
Solved! Go to Solution.
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:
Proud to be a Super User!
Paul on Linkedin.
Good point! You can solve it for example by "inflating" the date expression: INT(table[Date]) * 100000000000000 + table[order number]
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
Dear @PaulDBrown ;
thank you very much ,
sort measure gave error so i coreccted as below , am i right ?
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:
Proud to be a Super User!
Paul on Linkedin.
dear @PaulDBrown ;
i noticed that when order number starts to be 4 digit , index calculating is working wrongly, as you see for below picture material C even has April delivery date index number coming=3
the reason is ; order umber "1000" because of being 4 digit making concateate column very big number
can we solve this issue ?
Good point! You can solve it for example by "inflating" the date expression: INT(table[Date]) * 100000000000000 + table[order number]
Proud to be a Super User!
Paul on Linkedin.
@erhan_79
nobody's perfect! But thank you for the compliment!
Happy to have helped!
Proud to be a Super User!
Paul on Linkedin.
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,
Proud to be a Super User!
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 ?
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
101 | |
97 | |
66 | |
59 |