cancel
Showing results 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

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

5 REPLIES 5
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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
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]))

Helper II

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

Helper II

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.

Helper II

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

Announcements

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