cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

## Create a "priority list"

Hi All,

I'm having a bit of problem trying to find the right formula for my data problem.

I have 3 columns PO Number, PO Age, Backorders, Overdues.

I would like to create some sort of priority list that would look like this : the oldest PO that can clear the most Overdues and the most Backorders. Column overdues should have the highest priority.

What I was thinking is to give each column some sort of "weight"(column overdues having the biggest weight) and sort by that but im not entirely sure how to put that into a formula.

I've also tried to sort my dataset by multiple columns (Overdues,Backorders,Age) but I believe that is not solving my problem as it will put the oldest PO at the bottom of the dataset.

Example dataset

1 ACCEPTED SOLUTION
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

The concept is to have ranking number for each column, and then give a weight to the each ranking.

I tried to create a sample pbix file like below, and I hope the below solution can provide you the idea on creating a solution for your data model.

``````Priority CC =
Data,
"@priority",
RANKX ( Data, Data[Overdues],, ASC ) * 100000000
+ RANKX ( Data, Data[Backorders],, ASC ) * 10000
+ RANKX ( Data, Data[PO Age in days],, ASC )
)
"@rank", RANKX ( _addprioritycolumn, [@priority],, DESC )
)
RETURN
MAXX (
FILTER ( _addrankcolumn, Data[PO Number] = EARLIER ( Data[PO Number] ) ),
[@rank]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Helper II

@Jihwan_Kim Exactly what  i needed! Thank you very much.

Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

The concept is to have ranking number for each column, and then give a weight to the each ranking.

I tried to create a sample pbix file like below, and I hope the below solution can provide you the idea on creating a solution for your data model.

``````Priority CC =
Data,
"@priority",
RANKX ( Data, Data[Overdues],, ASC ) * 100000000
+ RANKX ( Data, Data[Backorders],, ASC ) * 10000
+ RANKX ( Data, Data[PO Age in days],, ASC )
)
"@rank", RANKX ( _addprioritycolumn, [@priority],, DESC )
)
RETURN
MAXX (
FILTER ( _addrankcolumn, Data[PO Number] = EARLIER ( Data[PO Number] ) ),
[@rank]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors