If the two value is same in a column then check the other column

[Delal]

Hello Everyone,

We have the dataset of projects, and each project has their status and start date (In the same table), and we are using the Gantt chart for illustration.

We want to sort the projects first based on a specific sequence of status ( END, Pend, HOLD, Work, LOST, RESV, Plan and Wish) and then start date on the timeline. However, I am having a problem with the two projects with the same status.

For instance;

Project Name	Project Status	Project Start Date
Project A	END	03/03/2014
Project B	END	01/01/2014

As you can see, the two projects have the same status, but somehow, the project on above plotted first, but our goal is to plot the below one, because it is started earlier than the above one.

I am looking for a solution that first checks the project's status, and if the two projects have the same status, it plots the project with the earliest start date on the timeline.

I am kindly requesting a support. Thank you for your answers/solutions.

Regards,

Delal

[v-luwang-msft]

Hi @Delal ，

Test  Sort by column:

When sort by Project name:

And sort by  Project Start date:

Wish it is helpful for you!

Best Regards

Lucien

[Delal]

Hello Lucien,

Thank you for your answer, but I am afraid this is not the solution I am looking for. Maybe I should have been more specific.

First of all, to illustrate projects, we are using a custom visualization called "xViz Gantt Chart," which allows precisely one sorting. However, I already manipulated this by creating a custom column (with if statements).

Custom column I created:

if [PRJ Order Category]="Development with no product / customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "END" then 1

else if [PRJ Order Category]="Development with no product /customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "PEND" then 2

else if [PRJ Order Category]="Development with no product / customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "HOLD" then 3

else if [PRJ Order Category]="Development with no product / customer reference" and  [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "WORK" then 4

else if [PRJ Order Category]="Development with no product / customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "LOST" then 5

else if [PRJ Order Category]="Development with no product / customer reference" and  [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "RESV" then 6

else if [PRJ Order Category]="Development with no product / customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "PLAN" then 7

else if [PRJ Order Category]="Development with no product / customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "WISH" then 8

// Development with product and customer reference
//----------------N/A------------------------------

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "END" then 9

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "PEND" then 10

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "HOLD" then 11

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "WORK" then 12

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "LOST" then 13

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "RESV" then 14

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "PLAN" then 15

else if [PRJ Order Category]="Development with product and customer reference" and [Torque_Level_Based_on_STGR] = "N/A" and [PRJ Status] = "WISH" then 16

As you can see, there is a sequence of numbers. I need to add another "and" statement, which indicates that if the PRJ status is the same, check the earliest one

Regards,

Delal

[v-luwang-msft]

Hi @Delal ,

Could you pls share your detail pbix file? And remember to remove confidential data.

Best Regards

Lucien