cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SadStatue Frequent Visitor
Frequent Visitor

retrieve Max value based on other columns

Hi Everybody,

 

I have a table "MainTable" as follow:

IDcompleteNamefromto
8827/06/2019Elly5/09/201912/10/2019
8827/06/2019Ann4/01/201910/10/2019
8827/06/2019Lary1/07/201710/10/2019
2227/06/2019Joe5/09/201912/10/2019
2227/06/2019Henry4/01/201910/06/2019
2227/06/2019Adam1/07/201710/10/2019
531/07/2019Mary5/01/201912/10/2019
531/07/2019Ferry4/01/201910/10/2019
531/07/2019Lucy1/07/201710/10/2019

Each ID has a single Complete Date, but has different name with different From & To dates.

What I want to extract from this table is: Getting the maximum From date for each ID where the Complete date is between From & To date. So the result table should be something like that:

IDCompleteNamefromto
8827/06/2019Ann4/01/201910/10/2019
2227/06/2019Adam1/07/201710/10/2019
531/07/2019Mary5/01/201912/10/2019

I would be glad if anyone can help me deal with the situation whether using DAX or Power querry.

 

Cheers,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: retrieve Max value based on other columns

Hi @SadStatue 

 

Try this M code.

Please see attached file as well

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrBQ0lEKySgtKk5JrNRR8CrNS1UwMtdRMDIwtATKuObkVKIoCE4tKEnNTUotUjCFKwpOLCktAsv6J5fkg+QMjaCSsToErXDMywOSbkWZENnEvNLEokoFE7g8QifcdAPiTfcBGobiRK/SnEoFQ7C8OWHTjYzwmu6Vn0pp6BCwwSM1D+wB3OHjm5+H0EmK4x1TEnMpCBpTYxTL4VrBbsIIdaiziQ4WfKa7pRYRCBMKnO5Tmkx2gokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, complete = _t, Name = _t, from = _t, to = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"complete", type date}, {"Name", type text}, {"from", type date}, {"to", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "complete"}, {{"AllRows", each _, type table [ID=number, complete=date, Name=text, from=date, to=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max(
    Table.SelectRows([AllRows],(x)=>[complete]>=x[from] and [complete]<=x[to]),
    "from")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "from", "to"}, {"Name", "from", "to"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"})
in
    #"Removed Columns"

 

 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: retrieve Max value based on other columns

Hi @SadStatue 

 

Try this M code.

Please see attached file as well

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrBQ0lEKySgtKk5JrNRR8CrNS1UwMtdRMDIwtATKuObkVKIoCE4tKEnNTUotUjCFKwpOLCktAsv6J5fkg+QMjaCSsToErXDMywOSbkWZENnEvNLEokoFE7g8QifcdAPiTfcBGobiRK/SnEoFQ7C8OWHTjYzwmu6Vn0pp6BCwwSM1D+wB3OHjm5+H0EmK4x1TEnMpCBpTYxTL4VrBbsIIdaiziQ4WfKa7pRYRCBMKnO5Tmkx2gokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, complete = _t, Name = _t, from = _t, to = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"complete", type date}, {"Name", type text}, {"from", type date}, {"to", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "complete"}, {{"AllRows", each _, type table [ID=number, complete=date, Name=text, from=date, to=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max(
    Table.SelectRows([AllRows],(x)=>[complete]>=x[from] and [complete]<=x[to]),
    "from")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "from", "to"}, {"Name", "from", "to"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"})
in
    #"Removed Columns"

 

 

View solution in original post

SadStatue Frequent Visitor
Frequent Visitor

Re: retrieve Max value based on other columns

Hi Zubair,

 

Thank you so much for your response. It is working as I wanted.

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 141 members 1,782 guests
Please welcome our newest community members: