Forum Discussion

acretney's avatar
acretney
Frequent Visitor
9 years ago
Solved

How do i sort a column with duplicate values by another column

Hi all,   I am trying to create a 'stacked bar chart' visual that will show me financial information corresponding to tasks coming from a project plan. (See below)   The tasks are currently...
Need Help
  • acretney's avatar
    acretney
    9 years ago

    Thanks Angelia,

     

    That fixed the calculated column however when i try to sort the 'taskname' column by it i get the following error

    "This column cant be sorted by a column that is already sorted, directly or indirectly by this column"

     

    I've actually implemented a workaround by concatenating the 'TaskIndex' and 'TaskName' values and sorting by this instead. So now i have..

    ID1 Establish routine

    ID2 Set Trap

    ID3 Drop Anvil

    ID4 Chug a beer

     

    It's not ideal as i would prefer the task ID not to display in the visual however at this stage its a cosmentic issue rather than a functional one.

     

    Thanks anyway for your efforts

     

    Kind regards

    Alex

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi acretney,

According to the error message, only the 'start date'column which has unique value for each value in 'TaskName' column can be used to sort the 'TaskName' column.

In this scenario, I would suggest you to create a new calculated column in the same table to calculate the MAX date for each 'TaskName'. The formula below to create the calculated column is for your reference.

MaxDate =
VAR currentTaskName = 'Table1'[TaskName]
RETURN
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER ( ALL ( Table1 ), 'Table1'[TaskName]  = currentTaskName
    )


Best Regards,
Angelia

 

  • acretney's avatar
    acretney
    Frequent Visitor
    9 years ago

    Hi Angelia,

     

    Thanks for the quick response. Unfortunately no dice on this occassion. I get the following error. Sorry im not really a codey person and need abit of hand holding ;)

     

     

     How do people actually go about attaching files within these posts? I could send you the pbix if it helps?

     

    Kind regards

    Alex

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      acretney

       

      Hi. 

       

      Remove the ) before the = and the error will be fixed

       

       

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi acretney,

      I edit the reply, as Vvelarde posted, please remove the ) as follows. And check if it works fine.

      MaxDate =
VAR currentTaskName = 'Table1'[TaskName]
RETURN
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER ( ALL ( Table1 ), 'Table1'[TaskName]  = currentTaskName
    )



      Thanks,
      angelia

       

      • acretney's avatar
        acretney
        Frequent Visitor
        9 years ago

        Thanks Angelia,

         

        That fixed the calculated column however when i try to sort the 'taskname' column by it i get the following error

        "This column cant be sorted by a column that is already sorted, directly or indirectly by this column"

         

        I've actually implemented a workaround by concatenating the 'TaskIndex' and 'TaskName' values and sorting by this instead. So now i have..

        ID1 Establish routine

        ID2 Set Trap

        ID3 Drop Anvil

        ID4 Chug a beer

         

        It's not ideal as i would prefer the task ID not to display in the visual however at this stage its a cosmentic issue rather than a functional one.

         

        Thanks anyway for your efforts

         

        Kind regards

        Alex