Max by ID from another table

[Anonymous]

I've tried a few solutipons to similar posts and can't find one that works. I have a table with multiple records per ID, some of which have a number in them (calculated field). My other table has one record per ID. I need to pull over the maximum value from Table 1 for that ID. 

Table1:

ID	Number
INC000002007779
INC000002007779	0.2
INC000002007779
INC000002007779	46.37
INC000002028105
INC000002028105	0.13
INC000002028105
INC000002050489	0
INC000002050489
INC000002050489	93.25
INC000002050489

Table2. I need to pull in the max number from table1 for each ID.

ID	Max Number
INC000002007779	46.37
INC000002050489	93.25
INC000002028105	0.13

[Anonymous]

And if you want to use DAX

Table2 = 
SUMMERIZE(
  Table1,
  Table1[Id],
  "Max Number",CALCULATE(Max(Table1[Number]))
)

[Anonymous]

Kristjan, I simplified my data and possibly left out some key pieces. My Table2 is an already existing table with other columns that I left out of my example. My Number column in Table1 is a calculated measure, so it won't pull into a new summarized table.

[Anonymous]

To get the max value from table 1 to table 2 into a calculated column you can

MaxValueFromTable1 = 
VAR T2id = Table2[Id]
RETURN
MAXX(
  FILTER(
    Table1,
    Table1[Id] = T2id
  ),
  Table1[Number]
)

[Anonymous]

Have you tried using Power Query for this?  If you load your table 1 below and use the Group by it should give you the output you are looking for:

[Anonymous]

mark, my number column is calculated, so unfortunately it doesn't show up in power query. I guess an alternative would be to calculate the column in power query, but I'm not sure of the syntax. Would you happen to know how to get the same results in PQ?

In this formula, "Audit" is Table1 from my previous example, Incident is my ID column and PriorityNum and AuditDate are other columns not included in my simplified example.

Number =

VAR _mindatetime0or1 =

CALCULATE (

MIN ( Audit[AuditDate] ),

ALLEXCEPT ( 'Audit', 'Audit'[Incident] ),

FILTER ( 'Audit', 'Audit'[Priority Num] = 1 || 'Audit'[Priority Num] = 2 )

)

RETURN

DATEDIFF (

CALCULATE (

MIN ( Audit[AuditDate] ),

ALLEXCEPT ( 'Audit', 'Audit'[Incident] )

),

_mindatetime0or1,

MINUTE

)

/ 60