RankX problems

[ColinH]

Hi, I struggle with RANKX when filtering. Can someone help me with this problem, please?

I have a table that lists soccer seasons with teams. I've created a new column (AllSeasonsFiling) that, when sorted max to min will give their position in that season's league table. All I need is to be able to filter the data by season to get its rankx number. In the screenshot below you'll see the data. It's all in one table.  I'm not sure whether I should create a measure or a calculated column.

Expected result would be a chart which shows a team's position by year.

[smpa01 · Accepted solution]

@ColinH  you can create a column like following

Column = RANKX(FILTER('Table 1','Table 1'[Season]=EARLIER('Table 1'[Season])),'Table 1'[AllSeasonsFiling],,DESC)

yet to figure out the measure

[parry2k]

@ColinH It's all good, we all learn from each other. Glad you have multiple solutions. Cheers!!

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@ColinH here is your measure, ofcourse you already have the solution but we always want to avoid adding a column (where possible):

Rank Team Season = 
RANKX ( 
    FILTER ( 
        ALL ( TeamSeason ),
        TeamSeason[Season] = MAX ( TeamSeason[Season] ) 
    ), 
    CALCULATE ( MAX ( TeamSeason[AllSeasonsFiling] ) ), , 
    DESC 
)

[ColinH]

That's fantastic also! It works perfectly. Again, many thanks for being patient with a noob here.

[parry2k]

@ColinH I think this is what you want

Rank Team Season = 
RANKX ( 
    FILTER ( 
        ALLSELECTED ( TeamSeason ), 
        TeamSeason[Team] = MAX ( TeamSeason[Team] ) 
    ), 
    CALCULATE ( MAX ( TeamSeason[AllSeasonsFiling] ) ), , 
    DESC 
)

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ColinH]

Thank you but no, that's not correct. Yours returns a value of 1 for 1993-94 when it should have been 4. I really appreciate you taking the time to reply though.

[smpa01]

@ColinH  please provide sample data in table format here.

[ColinH]

Apologies, @smpa01 hopefully I've done this correctly

Season	Team	AllSeasonsFiling
1992-93	Arsenal	5602040
1992-93	Aston Villa	7417057
1992-93	Blackburn	7122068
1992-93	Chelsea	5597051
1992-93	Coventry	5195052
1992-93	Crystal Palace	4887048
1992-93	Everton	5298053
1992-93	Ipswich	5195050
1992-93	Leeds	5095057
1992-93	Liverpool	5907062
1992-93	Man City	5705056
1992-93	Man Utd	8436067
1992-93	Middlesbrough	4379054
1992-93	Norwich	7196061
1992-93	Nottingham F	3979041
1992-93	Oldham	4889063
1992-93	QPR	6308063
1992-93	Sheff Utd	5201054
1992-93	Sheff Weds	5904055
1992-93	Southampton	4993054
1992-93	Tottenham	5894060
1992-93	Wimbledon	5401056
1993-94	Arsenal	7125053
1993-94	Aston Villa	5696046
1993-94	Blackburn	8427063
1993-94	Chelsea	5096049
1993-94	Coventry	5598043
1993-94	Everton	4379042
1993-94	Ipswich	4277035
1993-94	Leeds	6825065
1993-94	Liverpool	6004059
1993-94	Man City	4489038
1993-94	Man Utd	9242080
1993-94	Newcastle	7741082
1993-94	Norwich	5304065
1993-94	Oldham	3974042
1993-94	QPR	6001062
1993-94	Sheff Utd	4182042
1993-94	Sheff Weds	6422076
1993-94	Southampton	4283049
1993-94	Swindon	2947047
1993-94	Tottenham	4293052
1993-94	West Ham	5392048
1993-94	Wimbledon	6503056
1994-95	Arsenal	5103052
1994-95	Aston Villa	4795051
1994-95	Blackburn	8941080
1994-95	Chelsea	5395050
1994-95	Coventry	4982044
1994-95	Crystal Palace	4485034
1994-95	Everton	4993044
1994-95	Ipswich	2643036
1994-95	Leeds	7321059
1994-95	Leicester	2865045
1994-95	Liverpool	7428065
1994-95	Man City	4889053
1994-95	Man Utd	8849077
1994-95	Newcastle	7220067
1994-95	Norwich	4283037
1994-95	Nottingham F	7729072
1994-95	QPR	6002061
1994-95	Sheff Weds	5092049
1994-95	Southampton	5398061
1994-95	Tottenham	6208066
1994-95	West Ham	4996044
1994-95	Wimbledon	5583048

[smpa01]

@ColinH  you can create two measures like this

_allSeasonFiling = MAX('Table 1'[AllSeasonsFiling])

_rank = RANKX(ALLSELECTED('Table 1'[Team]),[_allSeasonFiling],,DESC)

[ColinH]

Hi, thank you so much, but it's not quite doing what I'd hoped - probably my fault for explaining badly. I wish the filter to be by team, and the result to be a table showing that team's position over multiple years. Your solution has it the other way round. I'm afraid I don't seem to be able to upload a pbix like you, so I can only show a screenshot. Here Arsenal are selected and they are incorrectly evaluating to rank 1. (This is using your provided pbix, not mine, so the data is the same as I attached earlier)

 

[smpa01]

@ColinH  so with this given selection, what values do you expect dax to return? 

[ColinH]

I'd expect Arsenal 1992-93 to show 10 (the 10th highest _allseasonfiling), 1993-94 to show 4 and, given I provided incomplete data for 1994-95, I'd expect that to show 12.

[smpa01 · Accepted solution]

@ColinH  you can create a column like following

Column = RANKX(FILTER('Table 1','Table 1'[Season]=EARLIER('Table 1'[Season])),'Table 1'[AllSeasonsFiling],,DESC)

yet to figure out the measure

[ColinH]

Gosh, that's it. I would never have got that in a month of Sundays. I've never even heard of EARLIER. Thank you so much for taking the time to help, it's very kind of you.