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
andrewbt
Helper II
Helper II

Maximum values across two tables

Hi all

 

I'm trying to find a maximum value for a particular ID across two tables but not having much success. 

For example:

  • Table 1 has many columns, including two: "ID" and "Score".  Any ID can have multiple scores.
  • Table 2 is a result of applying SUMMARISE to a different source and has only two columns, "ID" and "Max Score".  Only one row per ID
  • There's a many to 1 relationship between Table 1 and Table 2 based on ID

I am trying to SUMMARISE Table 1 and create a column which has the higher of "Score" from Table 1 and "Max score" from Table 2.

I could get the two results alongside each other as in my code below, but couldn't find a way to get a single maximum of the two.  I tried having two arguments for MAX and also played with MAXX but didn't get anywhere.

 
MyAttempt = SUMMARIZE('Table1',Table1[ID],"Max1",MAX('Table1'[Score]),"Max2",MAX('Table2'[Max Score]))

 

I'd be very grateful for any pointers.

Thanks

Andrew

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@andrewbt , not very clear. see if this measure can help

MyAttempt = maxx(SUMMARIZE('Table1',Table1[ID],"Max1",MAX('Table1'[Score]),"Max2",MAX('Table2'[Max Score])),max([Max1],[Max2]))

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@andrewbt - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@andrewbt , not very clear. see if this measure can help

MyAttempt = maxx(SUMMARIZE('Table1',Table1[ID],"Max1",MAX('Table1'[Score]),"Max2",MAX('Table2'[Max Score])),max([Max1],[Max2]))

Thanks again @amitchandak - I hadn't understood how to use your solution, but persevered and it's working now 🙂

Thanks @amitchandak 

 

I wasn't sure how to add it as a measure, but copy/pasting it into the definition of my table I received an error "The expression specified in the query is not a valid table expression".  I provided more details on my question as suggested by Greg.

Thanks for the pointers @Greg_Deckler!

 

I got here from the link from the help ribbon in Power BI and had never seen those posts.  The scenarios and and excel comparisons didn't provide a solution this time, nor my searches in the Forum, but they will definitely help me in the future.  

 

Here's a graphic showing where I'd got to:

ExampleExample

The source data itself:

Table 1  Table 2 
IDScore IDMax Score
112 15
114 223
221 331
331   

 

and I used the DAX expression 

MyAttempt = SUMMARIZE('Table1',Table1[ID],"Max1",MAX('Table1'[Score]),"Max2",MAX('Table2'[Max Score]))

to get the table shown above, but I'm trying to get a column "TargetMax" as below:

IDMax1Max2TargetMax
114514
2212323
3313131

 

 

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.