Forum Discussion
RANKX between DATES
Hello everyone
I'm trying to rank based on the PAY figures, but I'd like the start of the classification over the dates.
Example:
| Date | HR ID | Gender | Pay | Range |
| 01/04/2019 00:00 | 70458028 | F | 5.64 euros | 1 |
| 01/04/2019 00:00 | 87996123 | F | 8.18 euros | 2 |
| 01/04/2019 00:00 | 87998596 | M | 8.19 euros | 3 |
| 01/04/2019 00:00 | 88115887 | M | 8.28 euros | 4 |
| 01/04/2019 00:00 | 88122916 | M | 8.31 euros | 5 |
| 01/04/2019 00:00 | 87933369 | M | 8.42 euros | 6 |
| 01/04/2019 00:00 | 88178047 | M | 8.47 euros | 7 |
| 01/04/2019 00:00 | 88177948 | M | 8.47 euros | 8 |
| 01/04/2019 00:00 | 88177651 | M | 8.47 euros | 9 |
| 01/04/2019 00:00 | 88176760 | M | 8.47 euros | 10 |
| 01/03/2019 00:00 | 70458028 | F | 5.64 euros | 1 |
| 01/03/2019 00:00 | 87996123 | F | 8.18 euros | 2 |
| 01/03/2019 00:00 | 87998596 | M | 8.19 euros | 3 |
| 01/03/2019 00:00 | 88115887 | M | 8.28 euros | 4 |
| 01/03/2019 00:00 | 88122916 | M | 8.31 euros | 5 |
| 01/03/2019 00:00 | 87933369 | M | 8.42 euros | 6 |
| 01/03/2019 00:00 | 88178047 | M | 8.47 euros | 7 |
| 01/03/2019 00:00 | 88177948 | M | 8.47 euros | 8 |
| 01/03/2019 00:00 | 88177651 | M | 8.47 euros | 9 |
| 01/03/2019 00:00 | 88176760 | M | 8.47 euros | 10 |
I created a calculated column with DAX: RANKX(ALL('Table'[PAY]), 'Table'[PAY], ,DESC)
Anonymous , if you are creating the column refer 2nd one and if you are creating measure 1st one
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleThink expression should give you the calculated column you are looking for. I added a month column, but it would work with the data you posted if you just replace [Month] with [Date] in both places.
Rank Same Month = var currentmonth = PayTable[Month]var ranksamemonth = RANKX(FILTER(PayTable, PayTable[Month]=currentmonth), PayTable[Pay],,ASC)return ranksamemonthIf this works for you, please mark it as the solution. Kudos are appreciated too.Regards,Pat@Rich_coutinho , if you are creating the column, see the 2nd and if you are creating the 1st measure
For range, see these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
6 Replies
Anonymous , if you are creating the column refer 2nd one and if you are creating measure 1st one
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleThink expression should give you the calculated column you are looking for. I added a month column, but it would work with the data you posted if you just replace [Month] with [Date] in both places.
Rank Same Month = var currentmonth = PayTable[Month]var ranksamemonth = RANKX(FILTER(PayTable, PayTable[Month]=currentmonth), PayTable[Pay],,ASC)return ranksamemonthIf this works for you, please mark it as the solution. Kudos are appreciated too.Regards,PatHi,
Why do you want to create a calculated column formula? Why not a measure? Please show your expected result.
Hi Ashish_Mathur ,
It must be as colum as i need this info to be used inside of a graph as Axis X.
I've amended the DAX for :
RANKX(FILTER('Table', 'Table'[DATE]=EARLIER('Table'[DATE])),
'Table'[PAY])And I got th result as I was expeting.
Hi Anonymous ,
If you want a calculate column, the Mahoneypat’s solution works fine.
But if you want to create a measure, you can refer the following measure.
Measure = var day_ = MONTH(MAX('Table'[date])) var year_ = YEAR(MAX('Table'[date])) return IF( HASONEVALUE('Table'[date]), RANKX ( FILTER(ALLSELECTED('Table'[date]),MONTH('Table'[date])=day_ && YEAR('Table'[date])=year_), CALCULATE(SUM('Table'[pay])), , ASC, DENSE ))If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Rich_coutinho , if you are creating the column, see the 2nd and if you are creating the 1st measure
For range, see these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
