Main Assignment by ID

[RicLup]

Hi guys;

I would like you can help me, I need to know the main assignment grouped by ID, taking into consideration the following rules to determine it:

Example original Table:

ID	Percent	Start Date	End Date
1	30	January 10, 2021	January 10, 2022
1	50	March 1, 2021	December 31, 2021
1	20	June 1, 2021	December 31, 2021
2	100	January 1, 2021	February 20, 2022
3	50	April 1, 2021	April 30, 2022
4	100	May 1, 2021	December 31, 2021
4	100	March 1, 2021	November 30, 2021
5	100	January 1, 2021	January 30, 2022
5	100	February 1, 2021	January 30, 2022

1.-  The first option is with the ID number one, max assignment percent with differents end date and.

2.- the second option is with the ID number two and three, those ID only have a unique assignment

3.-  the third option is  with the ID number four, this ID have two assignments with the same percent, so here I need to take the max end date

4.- the fourth option is with the ID number five, this ID have the same percent and End Date, so here I need to take the earliest Start Date.

This is the final table with the column calculate at the end

ID	Percent	Start Date	End Date	Main Assignment
1	30	January 10, 2021	January 10, 2022	no
1	50	March 1, 2021	December 31, 2021	Main Assignment
1	20	June 1, 2021	December 31, 2021	no
2	100	January 1, 2021	February 20, 2022	Main Assignment
3	50	April 1, 2021	April 30, 2022	Main Assignment
4	100	May 1, 2021	December 31, 2021	Main Assignment
4	100	March 1, 2021	November 30, 2021	no
5	100	January 1, 2021	January 30, 2022	Main Assignment
5	100	February 1, 2021	January 30, 2022	no

Regards and thanks for your time.

[Vera_33]

Hi @RicLup 

Here is one way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2ABKGBrpeiXm6RoZIbCOlWB2IElOwEl3fxCKICmNDXZfUZBAbpsIIosKrNA+bCiOwqVAlMGuMDHTdUpNg1hgjrHEsgFljAGFDVJggGeKbWInNHlQlCFP88stgSkyxOcUYxcfISsAuRFMSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Percent = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Percent", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"maxPer", each List.Max([Percent]), type nullable number}, {"minStart", each List.Min([Start Date]), type nullable date}, {"maxEnd", each List.Max([End Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Added Custom", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each
[a= if [Percent]=[Added Custom][maxPer]{0} then 4
else 0,
b= if [End Date]=[Added Custom][maxEnd]{0} then 2
else 0,
c= if [Start Date]=[Added Custom][minStart]{0} then 1
else 0,
d=a+b+c][d]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Main Assignment", (x)=> if x[Custom]=List.Max( Table.SelectRows(#"Added Custom",each x[ID]=[ID])[Custom])then "Main Assignment" else "no"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Added Custom", "Custom"})
in
    #"Removed Columns"

[RicLup]

Hi @Vera_33 the logic works perfect thank you, I have been problems to apply the code with a large table with 13K rows. when I close and apply the query en Desktop, it takes a long time and don´t finish the query changes.

Do you know if i need some aditional consideration to improve the preformance and the query can be applied correctly?

Thanks for follow up my topic.

[Vera_33 · Accepted solution]

Hi @RicLup 

Given the large table, I don't see any performance improvement in M code using groupby or other ways, you may try DAX calculated column with the same logic, modify the Table name and Column name accordingly and let me know how it goes

Main Assignment = 
VAR CurID = yourTable[ID]
VAR T1 = FILTER(yourTable,[ID]=CurID)
VAR T2=
GENERATE(T1,
VAR a=IF([Percent]=MAXX(T1,[Percent]),4,0)
VAR b=IF([End Date]=MAXX(T1,[End Date]),2,0)
VAR c=IF([Start Date]=MINX(T1,[Start Date]),1,0)
return
ROW("check",a+b+c))
RETURN
IF(MAXX(FILTER(T2,[Percent]=EARLIER(yourTable[Percent])&&[Start Date]=EARLIER(yourTable[Start Date])&&[End Date]=EARLIER(yourTable[End Date])),[check])=MAXX(T2,[check]),"Main Assignment","no")

[RicLup]

Thanks @Vera_33, the queries works great,