Reset INDEX order when data sort does not give the results

[kfschaefer]

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.

[Eric_Zhang]

@kfschaefer

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 · Accepted solution]

---

@kfschaefer wrote:

thanks for the code, however, it seems to have and issue with the 4.

---

@kfschaefer

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.

[kfschaefer]

The code worked as far as creating a new index renumbering,  However, when I attempt to use it as a slicer the fliter order reverts back to the alpha sorting and not on the new index. 

I created a table, inserted the two columns LivesRange and SortID, still displaying the correct order, when I change the visualization to Slicer it here where the corrected order is lost.

I changed the Management relationship to have the LivesRange as the first linked to the Lives Range in the PowerBIData table via the LivesRange Name.   I would prefer not to have the SORTID displayed on my Visualization.

See attachments:

When I change from table to slicer it reverts.

I am also having the same issue with the Month Slicer.

[kfschaefer]

Thank your for your patience and assistance. 

A very gratefull newbie.

K

[Seth_C_Bauer]

@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. 

[kfschaefer]

However 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.

[Seth_C_Bauer]

@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?

[kfschaefer]

There is a minor issue with his solution, waiting on reply.