Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
acretney
Frequent Visitor

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)

 

Clip2.JPG

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...

 

Clip3.JPG

 

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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?

Screenshot_1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you in advance

 

 

 

 

v-huizhn-msft
Employee
Employee

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

 

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 😉

 

Capture1.JPG

 

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

 

Kind regards

Alex

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

 

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

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

 

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

Anonymous
Not applicable

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.

Hi @acretney,

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

Thanks,
Angelia

@acretney

 

Hi. 

 

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

 

 




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.