Assign Team based on Date

[Anonymous]

Hello,

I am having hard time assigning teams from an Employee reference table that has the employee name, their assign teams and date they join that time. I am using Lookupvalue to get the assign team and adding that to the sales table. But when I try to do this, a duplicate error shows up.

This is how the tables look:

This is how it should look(I use Sql to join the two tables):

Select S.*,
       EmpRef.Team


From Sales as S
OUTER APPLY(
SELECT TOP 1 E.Team
FROM EmployeeRefernce as E
WHERE	E.Employee = S.Employee
		AND E.EffectiveDay <= S.CreateDay
ORDER BY E.EffectiveDay DESC) AS EmpRef

Please any assist or help is greatly appreciated.Thank you so much!

[Anonymous]

Hello,

I am having hard time assigning teams from an Employee reference table that has the employee name, their assign teams and date they join that time. I am using Lookupvalue to get the assign team and adding that to the sales table. But when I try to do this, a duplicate error shows up.

This is how the tables look:

This is how it should look(I use Sql to join the two tables):

Select S.*,
       EmpRef.Team


From Sales as S
OUTER APPLY(
SELECT TOP 1 E.Team
FROM EmployeeRefernce as E
WHERE	E.Employee = S.Employee
		AND E.EffectiveDay <= S.CreateDay
ORDER BY E.EffectiveDay DESC) AS EmpRef

Please any assist or help is greatly appreciated.Thank you so much!

[TomMartens]

Hey @Anonymous

please do not crosspost your question across multiple forums.

See my remark to your question in the Desktop forum.

Regards,

Tom

[AlexisOlson]

You can do this in the query editor by merging on Employee, expanding the other two columns, filtering for EffectiveDay <= CreateDay, then doing a group by to take the team corresponding with the maximal remaining EffectiveDay.

Here's what the M code for this looks like:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRMjTUN9I3MjAyALJNzZRidaKVfPOh4qYwCRMDsIRPYlElkGeB0GFoaAiWCcnMBfLMERJGEB0odhhaguwwQLIDaoUhiG2AYoehEYZZKHaAtJgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, CreateDay = _t, UniteSold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"CreateDay", type date}, {"UniteSold", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Employee"}, EmployeeRefernce, {"Employee"}, "EmployeeRefernce", JoinKind.LeftOuter),
    #"Expanded EmployeeRefernce" = Table.ExpandTableColumn(#"Merged Queries", "EmployeeRefernce", {"EffectiveDay", "Team"}, {"EffectiveDay", "Team"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded EmployeeRefernce", each ([EffectiveDay] <= [CreateDay])),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Employee", "CreateDay", "UniteSold"}, {{"Team", each Table.Max(_, "EffectiveDay")[Team], type text}})
in
    #"Grouped Rows"

(Note that this won't work unless you already have the EmployeeRefernce table defined.)

DAX may be a bit simpler. You can define a calculated column like this:

Team = 
VAR LastEffectiveDay =
    CALCULATE (
        MAX ( EmployeeRefernce[EffectiveDay] ),
        EmployeeRefernce[Employee] = EARLIER ( Sales[Employee] ),
        EmployeeRefernce[EffectiveDay] <= EARLIER ( Sales[CreateDay] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( EmployeeRefernce[Team] ),
        EmployeeRefernce[Employee] = EARLIER ( Sales[Employee] ),
        EmployeeRefernce[EffectiveDay] = LastEffectiveDay
    )

[Ashish_Mathur · Accepted solution]

Hi,

Write these 2 calculated columns in the Sales table

Applicable effective day = CALCULATE(MAX('Employee reference'[EffectiveDay]),FILTER('Employee reference','Employee reference'[Employee]=EARLIER(Sales[Employee])&&'Employee reference'[EffectiveDay]<=EARLIER(Sales[CreateDay])))

Team = LOOKUPVALUE('Employee reference'[Team],'Employee reference'[EffectiveDay],Sales[Applicable effective day],'Employee reference'[Employee],Sales[Employee])

Hope this helps.

[TomMartens]

Hey @Anonymous ,

Maybe, you can consider to desribe the overall requirement in more detail and also to create a pbix file that contains sample data, but also reflects your data model (tables, relationships, calculated columns, and measures that might impact the result of the expected result). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, instead of using the manual input method, share the xlsx as well.

Regards,

Tom