Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
5 years ago

Visual sorting

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
Need Help
Show and Tell

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        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)