Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gk2go
Helper III
Helper III

Pick column value with max value in another column

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>)

5 REPLIES 5
Anonymous
Not applicable

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

Best
Darek

I'm open to an M query solution..

Anonymous
Not applicable

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

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

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors