Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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")
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"
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.
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.