Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |