Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Ranking by three criteria

Hi PBI community, I'm trying to rank a set of data (contracts), in ASCENDING order, according three criteria (columns): Year = 2011, 2012, , 2030 Month = 1, 2, , 12  Priority = 1, 2, 3, , 19 ...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    Yes Anonymous , but I would create a helper column. If in Power Query, create a custom column that is this:

    = [Year] * 10000 + [Month] * 10 + [Priority]

    That would create a column with 20200313 for example for 2020, March, priority 13.

    If you create a calculated column, it would be pretty much the exact same formula.

     

    I generally recommend custom columns in Power Query, but here, I don't think it makes a difference.

     

    Then use RANKX() using this column.  

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

Yes Anonymous , but I would create a helper column. If in Power Query, create a custom column that is this:

= [Year] * 10000 + [Month] * 10 + [Priority]

That would create a column with 20200313 for example for 2020, March, priority 13.

If you create a calculated column, it would be pretty much the exact same formula.

 

I generally recommend custom columns in Power Query, but here, I don't think it makes a difference.

 

Then use RANKX() using this column.  

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you very much edhans ! It worked!

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Excellent Anonymous. Glad to be of assistance. A little bit of tinkering with the model can sometimes make the DAX so much easier. 😁