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 alphabetically ordered and i would like to order them by 'start date' so we see a visual chronology

 

When i try to 'sort by column' within the Data view i get the following error...

 

 

This issue is that the project online data source will pull in data from hundreds of projects and it is inevitable i will end up with duplicate TaskNames each with different start dates.

 

Is anyone aware of a way around this restriction?

 

Kind regards

Alex

 

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

10 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi everyone!

     

    I have the same problem is described in this chat, but my data is organized in the same way that the solution provided. The error I get is that I can´t show the value as a percentage because a couple of rows are empty. Also, I can´t sort them because a couple of values ar equals. Why the error is still turning up whether "Axis-Copy" has no duplicate values?

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Thank you in advance