Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Max by ID from another table

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:

IDNumber
INC000002007779 
INC0000020077790.2
INC000002007779 
INC00000200777946.37
INC000002028105 
INC0000020281050.13
INC000002028105 
INC0000020504890
INC000002050489 
INC00000205048993.25
INC000002050489 

 

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

IDMax Number
INC00000200777946.37
INC00000205048993.25
INC0000020281050.13
Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    And if you want to use DAX

     

    Table2 = 
SUMMERIZE(
  Table1,
  Table1[Id],
  "Max Number",CALCULATE(Max(Table1[Number]))
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

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