Forum Discussion
Helper IVReset INDEX order when data sort does not give the results
Looking for solution to reset the INDEX numbering to allow for self determined numbering when Data Asc order does not return the correct results. See how the 3000+ comes before 501-3000. is there a way to manuallly reassign the index?
Need help with the following if statement for PowerBI
if [#"# Lives Range"] = "0-100" then 1 else if [#"# Lives Range"] = "101-500" then 2 else if [#"# Lives Range"] = "501-3000" then 3 else if [#"# Lives Range"] = "3000+" then 4 else if [#"# Lives Range"] = "New" then 5 else if [#"# Lives Range"] = "Runout" then 6
Trying to create a unique Index to place the list in correct order - due to the 3000+ value does not place it correctly in ascending order.
kfschaefer wrote:
thanks for the code, however, it seems to have and issue with the 4.
I thought you were adding a column in the modeling lab with DAX, the switch is a DAX function.
To add a column with the same logic in the Query Editor which Power Query is used, you can reference below expression.if [live range]="0-100" then 1 else( if [live range]="101-500" then 2 else( if [live range]="501-3000" then 3 else( if [live range]="3000+" then 4 else( if [live range]="New" then 5 else( if [live range]="Runout" then 6 else 999)))))
If you have any question, feel free to let me know.
9 Replies
kfschaefer I'm not really sure if this is what your after, but you can sorty your Lives Range column by the index column so it always returns in proper index order. Go to modeling tab - highlight the Lives Range - select Sort by column in top ribbon and sort by index.
kfschaeferHowever the 3000+ is sorted out of order when I use the Index column.
I need the 3000+ to be displayed after the 500-3000 record, hence the need for manual changing the index to correct this issue.
Thanks for your input.
kfschaefer It sounds like you just need another column to sort by. Eric_Zhang provides you with the path to create that new column, but you can manually build it as well. Just create a column that would correspond to the correct order and sort by that column for the order that you want... Is there something else I'm missing?
Eric_ZhangMicrosoft Employee
You can new a column in the modeling lab with the below expression.
NewIndex = SWITCH(Sheet1[live range],"0-100",1,"101-500",2,"501-3000",3,"3000+",4,"New",5,"Runout",6)
- kfschaefer
thanks for the code, however, it seems to have and issue with the 4.
- Eric_Zhang
kfschaefer wrote:
thanks for the code, however, it seems to have and issue with the 4.
I thought you were adding a column in the modeling lab with DAX, the switch is a DAX function.
To add a column with the same logic in the Query Editor which Power Query is used, you can reference below expression.if [live range]="0-100" then 1 else( if [live range]="101-500" then 2 else( if [live range]="501-3000" then 3 else( if [live range]="3000+" then 4 else( if [live range]="New" then 5 else( if [live range]="Runout" then 6 else 999)))))
If you have any question, feel free to let me know.
