Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nirvana_moksh
Impactful Individual
Impactful Individual

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 fields

 

Either in M or DAX I want a final table which gives me just one row per ID based on the Insert Date and for other ID's that are not present in Table 2 to return NULL:

 

 

Table 1 

 

IDNAMEROLE
1ABCDEVELOPER
2ADASQA
3MQEBA
4RTYAPM
5GJHTPM

 

 

Table 2 

 

IDTASKDURATIONINSERT DATE
1COMPLETE2 HRS6/22/2018 16:39
1INCOMPLETE3 HRS6/24/2018 15:39
1INCOMPLETE4 HRS6/24/2018 18:39
1INCOMPLETE3 HRS6/24/2018 15:39

 

Final Result 

 

IDTASKDURATIONINSERT DATE
1INCOMPLETE4 HRS6/24/2018 18:39
2nullnullnull
3nullnullnull
4nullnullnull
5nullnullnull

 

My DAX table function and M worked till now but is failing today showing weird results.

2 ACCEPTED SOLUTIONS
Phil_Seamark
Employee
Employee

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

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Anonymous
Not applicable

Yes there are many sites which explains how to do this. For example,Please refer the below URL which explains the left outer join in DAX.

 

https://curbal.com/blog/joining-table-in-power-bi-with-power-query-and-dax

 

Thanks

Raj

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

 

This M code works

 

let
    Source = Table.NestedJoin(Table1,{"ID"},Table2,{"ID"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"TASK", "DURATION", "INSERT DATE"}, {"TASK", "DURATION", "INSERT DATE"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table2", {"ID"}, {{"Max", each List.Max([INSERT DATE]), type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"ID", "Max"},Table2,{"ID", "INSERT DATE"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"TASK", "DURATION"}, {"TASK", "DURATION"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table1",{"ID", "TASK", "DURATION", "Max"})
in
    #"Reordered Columns"

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Phil_Seamark
Employee
Employee

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

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark - Hey Phil, I tried adding another field from Table 2 to the final outcome and for some reason it stopped returning the other columns, In order to add any more additional columns should that column be reference in every variable defined?

@Phil_Seamark thanksa lot again Phil, last time you gave me a similar solution and this was an expansion to that and like previously I again appreciate you taking out time to detail it out and laying out new DAX functions which always expands my knowledge. I am customizing this to my need, but it is working so far. Thanks a lot again!

Giving this a shot, my actual tables have more data fields than I listed but hopefully my tweaks take them all.

Anonymous
Not applicable

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

Maxdate1.PNG

 

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

 

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

Maxdate2.PNG

 

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

 

 

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


@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

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

Anonymous
Not applicable

Yes there are many sites which explains how to do this. For example,Please refer the below URL which explains the left outer join in DAX.

 

https://curbal.com/blog/joining-table-in-power-bi-with-power-query-and-dax

 

Thanks

Raj

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.