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

    • nirvana_moksh's avatar
      nirvana_moksh
      Impactful Individual
      8 years ago

      Thanks for the article Anonymous but the last step is something that I always have an issue with because of the PBI has joins in DAX and how it doesnt recognize the join columns and I was running into that again for some reason.