Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , 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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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