Forum Discussion

slanka's avatar
slanka
Helper I
7 years ago
Solved

Column reference from related table with filter

Hello People,

 

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

 

Projects:

Employee NameProject NameStart DateEnd Date
Emp1Proj13/27/20194/2/2019
Emp1Proj24/3/20194/5/2019
Emp1Proj34/8/20194/9/2019
Emp2Proj14/3/20194/8/2019
Emp2Proj44/9/20194/10/2019

 

Employee:

Employee NameCalenderDate
Emp13/27/2017
Emp13/28/2017
Emp13/29/2017
Emp13/30/2017
Emp13/31/2017
Emp14/1/2017
Emp14/2/2017
Emp14/3/2017
Emp14/4/2017
Emp14/5/2017
Emp14/8/2017
Emp14/9/2017
Emp24/3/2019
Emp24/4/2019
Emp24/5/2019
Emp24/8/2019
Emp24/9/2019
Emp24/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 NameCalenderDateProject Name
Emp13/27/2017Proj1
Emp13/28/2017Proj1
Emp13/29/2017Proj1
Emp13/30/2017Proj1
Emp13/31/2017Proj1
Emp14/1/2017Proj1
Emp14/2/2017Proj1
Emp14/3/2017Proj2
Emp14/4/2017Proj2
Emp14/5/2017Proj2
Emp14/8/2017Proj3
Emp14/9/2017Proj3
Emp24/3/2019Proj1
Emp24/4/2019Proj1
Emp24/5/2019Proj1
Emp24/8/2019Proj1
Emp24/9/2019Proj4
Emp24/10/2019Proj4

 

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

 

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

 

Please advise

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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]
    )
)

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    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.

    • parry2k's avatar
      parry2k
      Super User
      7 years ago

      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]
    )
)
      • slanka's avatar
        slanka
        Helper I
        7 years ago

        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]
    )
)

         

    • slanka's avatar
      slanka
      Helper I
      7 years ago

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