Visual sorting

[vjnvinod]

Hi,

I have created a calculated coloumn using the below Dax

Status = IF('GCSP & MSL'[Moved?]="Yes","Moved", IF( SEARCH("TBC",'GCSP & MSL'[Additional info],1,0)>0,"Under Discussion",

IF( SEARCH("NoNoNo",'GCSP & MSL'[Region&SLnow&oldSL&exception&TBC&Duplicate],1,0)>0 && SEARCH("CBS",'GCSP & MSL'[Region&SLnow&oldSL&exception&TBC&Duplicate],1,0)>0,"In Markets, Other SMU",

IF( SEARCH("Yes",'GCSP & MSL'[Region&Exception],1,0)>0,"Exceptions","To Move")))) and

created a visual like below

now i would like to sort this in the below order

Moved,

In Markets,

To move,

Under discussion,

Exceptions

I tried creating another calculated coloumn to achieve this using IF function, and its throwing me error

"failed to save modification to the server: A circular dependency detected.....)

below is the formula i used

sortingstatus = IF('GCSP & MSL'[Status]="Moved","1",

IF('GCSP & MSL'[Status]="In Markets, Other SMU","2",

IF('GCSP & MSL'[Status]="To Move","3",

IF('GCSP & MSL'[Status]="Under Discussion","4",

IF('GCSP & MSL'[Status]="Exceptions","5","")))))

any suggest on this will be helpful, as i am not able to sort the visual the way i want

[amitchandak]

@vjnvinod , this because the sort column is baed on the same column

create a new column

Status 1= MSL'[Status]=

sortingstatus = IF('GCSP & MSL'[Status]="Moved","1",
IF('GCSP & MSL'[Status]="In Markets, Other SMU","2",
IF('GCSP & MSL'[Status]="To Move","3",
IF('GCSP & MSL'[Status]="Under Discussion","4",
IF('GCSP & MSL'[Status]="Exceptions","5","")))))

now sort Status 1 on sortingstatus  and use Status 1 in your report

[vjnvinod]

@amitchandak 

it throwing up error

[amitchandak]

@vjnvinod , Try like

sortingstatus = Switch(True() ,
'GCSP & MSL'[Status]="Moved",1,
'GCSP & MSL'[Status]="In Markets, Other SMU",2,
'GCSP & MSL'[Status]="To Move",3,
'GCSP & MSL'[Status]="Under Discussion",4,
'GCSP & MSL'[Status]="Exceptions",5,10)

[vjnvinod]

@amitchandak 

it doesn't work either

see below same error

[amitchandak]

@vjnvinod , what is there between column name and Switch . I see two = .

This is a column in table 

[vjnvinod]

@amitchandak 

you provided the double "=" forumla in the first reply to this thread, i thought you might have some logic for that

see below you response

this because the sort column is baed on the same column

create a new column

Status 1= MSL'[Status]=

sortingstatus = IF('GCSP & MSL'[Status]="Moved","1",
IF('GCSP & MSL'[Status]="In Markets, Other SMU","2",
IF('GCSP & MSL'[Status]="To Move","3",
IF('GCSP & MSL'[Status]="Under Discussion","4",
IF('GCSP & MSL'[Status]="Exceptions","5","")))))

now sort Status 1 on sortingstatus  and use Status 1 in your report

[amitchandak]

@vjnvinod ,

Column 1

Status 1= MSL'[Status]

Column 2 

sortingstatus = IF('GCSP & MSL'[Status]="Moved","1",
IF('GCSP & MSL'[Status]="In Markets, Other SMU","2",
IF('GCSP & MSL'[Status]="To Move","3",
IF('GCSP & MSL'[Status]="Under Discussion","4",
IF('GCSP & MSL'[Status]="Exceptions","5","")))))

sort column 1 on column 2 and try 

[vjnvinod]

@amitchandak 

Perfect, worked like a charm 🙂

[amitchandak]

@vjnvinod ,  😀