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

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

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

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

      Hi acretney,

      Congratulations, you have resolved your issue. Please mark your solution as workaround, so that more people will benefit from it.

      Thanks,
      Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I agree this is not an idea solution. If there is a duplicate value, but it has a unique ID from another system, we should not need to remap these items to new names with a sort value as a prefix. In principle, I understand why powerBI has created this limitation, but we should be able to define hidden attributes which make duplicate values unique so that they can be sorted correctly in visualizations.

  • nithinmathew's avatar
    nithinmathew
    Frequent Visitor
    8 years ago

    Hi, Since 'calculate' doesn't work for calculated columns in direct query mode, how can I try this in direct query mode?

     

  • nithinmathew's avatar
    nithinmathew
    Frequent Visitor
    8 years ago

    IN direct query mode 'Calculate' function  cannot be used in calculated column. Do you have the solution for the same in Direct Query mode?