Column reference from related table with filter

[slanka]

Hello People,

I have two tables Project and Employee with structure as below,

Projects:

Employee Name	Project Name	Start Date	End Date
Emp1	Proj1	3/27/2019	4/2/2019
Emp1	Proj2	4/3/2019	4/5/2019
Emp1	Proj3	4/8/2019	4/9/2019
Emp2	Proj1	4/3/2019	4/8/2019
Emp2	Proj4	4/9/2019	4/10/2019

Employee:

Employee Name	CalenderDate
Emp1	3/27/2017
Emp1	3/28/2017
Emp1	3/29/2017
Emp1	3/30/2017
Emp1	3/31/2017
Emp1	4/1/2017
Emp1	4/2/2017
Emp1	4/3/2017
Emp1	4/4/2017
Emp1	4/5/2017
Emp1	4/8/2017
Emp1	4/9/2017
Emp2	4/3/2019
Emp2	4/4/2019
Emp2	4/5/2019
Emp2	4/8/2019
Emp2	4/9/2019
Emp2	4/10/2019

I would need to populate 3rd column in Employee table, with filters as below:

1) IF Calender Date (which is generated from CALENDER function) falls between Start Date and End Date for any employee, populate that respective project (Group by employee, filter needs to be applied)

Final table should look like this:

Employee Name	CalenderDate	Project Name
Emp1	3/27/2017	Proj1
Emp1	3/28/2017	Proj1
Emp1	3/29/2017	Proj1
Emp1	3/30/2017	Proj1
Emp1	3/31/2017	Proj1
Emp1	4/1/2017	Proj1
Emp1	4/2/2017	Proj1
Emp1	4/3/2017	Proj2
Emp1	4/4/2017	Proj2
Emp1	4/5/2017	Proj2
Emp1	4/8/2017	Proj3
Emp1	4/9/2017	Proj3
Emp2	4/3/2019	Proj1
Emp2	4/4/2019	Proj1
Emp2	4/5/2019	Proj1
Emp2	4/8/2019	Proj1
Emp2	4/9/2019	Proj4
Emp2	4/10/2019	Proj4

On words, i would need a calculated column in Employee table like,

CALCULATE('Project'[ProjectName], If(CalenderDate >= StartDate && CalenderDate <= EndDate), GroupBy(EmployeeName))

Please advise

[edhans]

I would use Power Query for this. See my example PBIX file below. The merge is done there and has the date logic in it.

Power Query Example

When you open the file, select EDIT QUERIES to see your original tables, and the final merged table that is actually loaded into Power BI for reporting.

The formula that was used is:

=if ([CalenderDate] >= [Start Date] and [CalenderDate] <= [End Date]) then true else false

then I simply filtered for true for the final table.

[slanka]

Sorry, I forgot to mention that my Employee table is calculated table, hence i cannot use EDIT QUERIES for that

[parry2k · Accepted solution]

@slanka use following DAX to add new column in your employee table

Project Name = 
CALCULATE( 
    MAX( Proj[Project Name] ), 
    FILTER( 
        Proj, 
        Emp[CalenderDate] >= Proj[Start Date]  && 
        Emp[CalenderDate] <= Proj[End Date] && 
        Proj[Employee Name] = Emp[Employee Name]
    )
)

[edhans]

@parry2k - do you have a link to an article or documentation as to why your solution works? I understand the logic of your CALCULATE() statement, but the Employee and Project tables cannot be related to each other, unless you use a MANY to MANY relationship.

But I left the tables unreleated and your solution works without any RELATED/RELATEDTABLE functions, so my real question is, why does this work, and what else works, without any relationships?

Is it as simple as CALCULATE() is itterating through each record of each table?

[slanka]

It Works! Thanks

---

@parry2k wrote:

@slankause following DAX to add new column in your employee table

Project Name = 
CALCULATE( 
    MAX( Proj[Project Name] ), 
    FILTER( 
        Proj, 
        Emp[CalenderDate] >= Proj[Start Date]  && 
        Emp[CalenderDate] <= Proj[End Date] && 
        Proj[Employee Name] = Emp[Employee Name]
    )
)

---