Maximum values across two tables

[andrewbt]

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

[Greg_Deckler]

@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.

[amitchandak · Accepted solution]

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

[andrewbt]

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

[andrewbt]

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.

[andrewbt]

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:

Example

The source data itself:

Table 1			Table 2
ID	Score		ID	Max Score
1	12		1	5
1	14		2	23
2	21		3	31
3	31

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:

ID	Max1	Max2	TargetMax
1	14	5	14
2	21	23	23
3	31	31	31