Ranking a column based on another column value

[OsmanEmi]

Hi,

I'm currently working on a dataset where I would need to display the location with the most ratings that are less than 3.

I can't seem to figure out the correct DAX functions to use to display this.

I have attached table below, any help would be greatly appreciated

Location name	Rating
London	5
Birmingham	4
Coventry	3
Basingstoke	4
Manchester	5
London	5
Birmingham	1
Coventry	1
London	2
Birmingham	2
Coventry	2
Birmingham	2
Coventry	3
Basingstoke	4

[amitchandak · Accepted solution]

@OsmanEmi , Try measures like

cnt = calculate(count(Table[Location name] ), filter(Table, Table[Rating]<=3))

// This will give the location
Top 1 Rank = CALCULATE(max(Table[Location name]),TOPN(1,all(Table[Location name]),[cnt],DESC),VALUES(Table[Location name]))