Value from another (many-to-many)-related table based on date with timerange

[Anonymous]

Hi, 

I have two tables (Table1 and Table2) which are in a Many to Many relation using the ID as key. 

Table1 is my table of employees and their duties. Table2 is my table with assignments. 

I want to see which employees are on duty for the assignments. Meaning the time of the assignment in table2 is within the FirstLogin and Lastlogout for the employee in table1. 

The result shoould be something like "NameIDAll, NameID1.." etc. in table2.

Ultimatively, I want to calculate the number of fines based on if the employee was in duty (logged in) and now it calculates two fines for John, Peter and Anne although Anne wasnt on logged in when the two fines happened. 

Thanks! 

Mikkel

[dax · Accepted solution]

Hi Mlchri,

The measure is hard to split column dynamically, so I think you could achieve this in M code, you could try below M code and refer to my sample to see whether it work or not

let
    Source = Table.NestedJoin(#"Table (2)", {"id"}, #"Table", {"id"}, "Table", JoinKind.FullOuter),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"id", "name", "nameid", "firstlogin", "lastlogin"}, {"Table.id", "Table.name", "Table.nameid", "Table.firstlogin", "Table.lastlogin"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "Custom", each if [assignment]<[Table.lastlogin] and [assignment]>[Table.firstlogin] then [Table.nameid] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table.id", "Table.name", "Table.nameid", "Table.firstlogin", "Table.lastlogin"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "assignmentid"}, {{"sum",  each Text.Combine([Custom],","), type text}, {"all", each _, type table [id=number, assignmentid=number, assignment=datetime, finddate=datetime, Custom=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"assignment", "finddate", "Custom"}, {"all.assignment", "all.finddate", "all.Custom"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded all",{"all.Custom"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns1", "sum", "sum - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "sum - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"sum - Copy.1", "sum - Copy.2", "sum - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"sum - Copy.1", Int64.Type}, {"sum - Copy.2", Int64.Type}, {"sum - Copy.3", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1")
in
    #"Removed Duplicates"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax ,

Thanks!

It works perfectly with some small adjustments to my own dataset, which is far more complex.