Forum Discussion

nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
8 years ago
Solved

Max Row Based on Insert Date

Hello,   So I have two solutions in place but they are causing some issues. Bascially, I have two tables:   1.) Table 1 has ID which is also present in Table 2 2.) Table 2 has ID and other data ...
Need Help
Show and Tell
Tips and Tricks
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi nirvana_moksh

     

    This calculated table gets close.  It looks long, but it's not that complicated.  I have attached a PBIX file

     

    Table = 
VAR x = SELECTCOLUMNS(
            Table2 ,
            "IDx",[ID] , 
            "TASK" ,[TASK] ,
            "DURATIONx" , 
            INT(SUBSTITUTE('Table2'[DURATION]," HRS","")) , 
            "INSERT DATE",[INSERT DATE]
            )
VAR y = 
    GROUPBY(
        x,
        [IDx],
        "MAX_DURATIONx", MAXX(CURRENTGROUP(),[DURATIONx])
        )
VAR z= 
    SELECTCOLUMNS(
        GENERATEALL(
            'Table1',
            FILTER(
                y,
                [ID] = [IDx]
                )
            ),
            "IDz",[ID],
            "MAX_DURATOINx",[MAX_DURATIONx]
            )
RETURN 
    SELECTCOLUMNS(
        GENERATEALL(
            z,
            FILTER(
                'Table2',[ID] = [ID] && 
                [MAX_DURATOINx] & " HRS" = 'Table2'[DURATION]
                )
            ),
            "ID",[IDz],
            "TASK",[TASK],
            "DURATION",[DURATION],
            "INSERT DATE",[INSERT DATE]
            )

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I just tried in different way.

 

Step 1 : Lets take your Table 2. Add new column with Max insert date for each ID.

 

Max_Insert_Date = CALCULATE(MAX(Table2[INSERT DATE]),FILTER(Table2,Table2[ID]= EARLIER(Table2[ID])))

 

Step 2: From step 1,Get the Latest record only

 

Table3 = FILTER(Table2,Table2[INSERT DATE]=Table2[Max_Insert_Date])

 

Step 3: Do a left outer join between Table 1 ( Original table - Just take ID column alone) and Table 3 ( derived in step 2- Select all columns but ID).

 

You can combine these steps as well to reduce the steps .

 

Thanks

Raj

 

 

nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
8 years ago

I am gettign error on the second step. The error is "the expression referrs to multiple columsn. Multiple columns cannot be converted to scalar value"

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    nirvana_moksh wrote:

    I am gettign error on the second step. The error is "the expression referrs to multiple columsn. Multiple columns cannot be converted to scalar value"

    Please do the second step in Modelling -> Nea Table and use the formula. SInce the formula returns multiple colums/ rows, we cant use it as measure/ calculated column.

     

    Thanks

    Raj

    • nirvana_moksh's avatar
      nirvana_moksh
      Impactful Individual
      8 years ago

      I See, for the last step are you suggesting to use the NATURALLEFTOUTERJOIN in Dax?