Pick column value with max value in another column

[gk2go]

I have this table:

Title                              | Path                                                |  Views

ABC Super Super Title  | www.example.com/abc.html          | 5

ABC Super Super Title  | www.example.com/abc.html          | 2

ABC Super Title            | www.example.com/abc.html          | 5

DEF Title                       | www.example.com/def                   | 2 

I want to extract this:

Path                                         | Top_title

www.example.com/abc.html   | ABC Super Super Title // Because it has a total of 7 views

www.example.com/def            | DEF Title // It's the only one for this path

I suppose this could be achieved in a calculated table, but I'm not sure of the DAX expression needed. 

Here is what I tried:

titleTable = SUMMARIZE(t, t[path], t[title], "title_views", sum(t[views]))

This gave me this table:

Title                              | Path                                                |  title_views

ABC Super Super Title  | www.example.com/abc.html          | 7

ABC Super Title            | www.example.com/abc.html          | 5

DEF Title                       | www.example.com/def                   | 2 

Then I was thinking to create yet another table grouped by path, and selecting the top title based on the title_views. I don't know how to do this..

topTitles = SUMMARIZE(titleTable, titleTable[path], "top_title", <DAX for the value of the title that has the highest value in title_views>)

[Anonymous]

Why are you doing this in DAX and not in Power Query? Why do you insist on using the wrong tool?

Best
Darek

[gk2go]

I'm open to an M query solution..

[Anonymous]

Here's the file with PQ transformations:

https://1drv.ms/u/s!ApyQEauTSLtO6m46sADmObtnJRe9

Please note that I had to deal with the possibility of duplicate titles for the same path with the same number of views. You have not explained how you are going to deal with this, so in such a case I only leave the title that's first in alphabetical order.

Best

Darek

[gk2go]

Nice @Anonymous . So, I see you are creating many one-line m query tables. Cannot they be combined in one/fewer?

[Anonymous]

You can do with the code whatever you want 🙂 I just showed you in a clear way how to do what you wanted in M.

Best
Darek