DAX - Segmentation

[potap]

Hello!

I am trying to find in which team was an employee when he executed a specific job.

Let's say I have these 2 tables :

Job table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]JobID[/TD]
[TD]TeamNum[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]2014-05-05[/TD]
[TD]ABC[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]2014-12-01[/TD]
[TD]ABC[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]2014-12-12[/TD]
[TD]XYZ[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

Team composition table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]TeamNum[/TD]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]1[/TD]
[TD]2014-01-01[/TD]
[TD]2014-12-31[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]2[/TD]
[TD]2014-01-01[/TD]
[TD]2014-06-30[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]3[/TD]
[TD]2014-07-01[/TD]
[TD]2014-11-30[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]2[/TD]
[TD]2014-12-01[/TD]
[TD]2014-12-31[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]3[/TD]
[TD]2014-01-01[/TD]
[TD]2014-08-31[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]1[/TD]
[TD]2014-09-01[/TD]
[TD]2014-12-31[/TD]
[/TR]
[TR]
[TD]Roger[/TD]
[TD]2[/TD]
[TD]2014-01-01[/TD]
[TD]2014-03-31[/TD]
[/TR]
</tbody>[/TABLE]

I can't get it done because I have the date and name criteria

Thank you very much!

 

[Eric W]

Assuming your tables are in columns A-D, and the first question mark is D2, then put this array formula in D2:

=MAX(--(B2>=$C$9:$C$15)*--(B2<=$D$9:$D$15)*--(A2=$A$9:$A$15)*$B$9:$B$15)

This is an array function, so use Control-Shift-Enter when you put it in the formula bar. Then copy it down to the next 2 cells.

It seems evident that your actual tables are in different places, but you should be able to see how to change it. Let me know how it works.

 

[potap]

Thanks but my actual job table has more than 5 millions rows so the solution has to be in DAX...

 

[Ozeroth]

You could try modifying the DAX for "events in progress"
(page 27 or page 21 of this document):
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

You would have to change it so that instead of counting rows you extract TeamNum.

 

[ImkeF]

Another option would be to move this task into the ETL-process using Power Query - depending on performance aspects (slower load vs. slower queries):

let
Source = Excel.CurrentWorkbook(){[Name="Jobs"]}[Content],
Merge = Table.NestedJoin(Source,{"Name"},TeamComp,{"Name"},"NewColumn",JoinKind.Inner),
Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"TeamNum", "StartDate", "EndDate"}, {"TeamNum.1", "StartDate", "EndDate"}),
CheckMatech = Table.AddColumn(Expand, "TeamNo", each if [Date] >= [StartDate] and [Date] <=[EndDate] then [TeamNum.1] else "out"),
FilterNonMatch = Table.SelectRows(CheckMatech, each ([TeamNo] <> "out")),
RemoveOtherCols = Table.SelectColumns(FilterNonMatch,{"JobID", "Name", "Date", "TeamNo"}),
ChgDateType = Table.TransformColumnTypes(RemoveOtherCols,{{"Date", type date}})
in
ChgDateType

LinkToFile

 

[potap]

Thanks, work great!