Forum Discussion

Maha1's avatar
Maha1
Icon for Helper II rankHelper II
3 years ago

create relationship for table with dates and month/year

Hello, I have 2 tables that I'm trying to link or merge them into one table using DAX. Each row in Table1 represent a date for an employee leave. Table2 represent employee's population in each month/year. I need to create a measure that will show percentage of employees on leave per day. Below are examples:

 

Table1 (note that no employee is repeated on the same day):

Leave DateEmp Name
1/1/2022ABC
1/1/2022GHI
1/2/2022ABC
1/2/2022DEF
1/2/2022GHI
2/10/2022ABC
2/11/2022ABC
2/12/2022ABC

 

 

Table2: (shows all active employees whether he/she had leave date or not)

Cal Year MonthEmp Name
2022 JanABC
2022 JanDEF
2022 JanGHI
2022 JanJKL
2022 FebABC
2022 FebDEF
2022 FebGHI
2022 FebJKL
2022 FebMNO
2022 FebPQR

 

 

Expected Results in the report:

DayNo. of Employees on leavePopulation% on leave
1/1/20223475%
1/2/20221425%
1/3/2022040%
1/4/2022040%
…. 4 
2/10/20221617%
2/11/20221617%
2/12/20221617%

 

 

Is it possible to create a combined table in DAX that will muliply each employee in a yearMonth in Table2 by number of days in a the month (below is an example), or is there a better way of creating a relationship using a linking date table?

 

DateEmp NameLeave Date
1/1/2022ABC1/1/2022
1/2/2022ABC 
1/3/2022ABC 
1/4/2022ABC 
1/5/2022ABC 
1/6/2022ABC 
1/7/2022ABC 
1/8/2022ABC 
1/9/2022ABC 
1/10/2022ABC 
1/11/2022ABC 
……ABC 
1/1/2022DEF 
1/2/2022DEF 
1/3/2022DEF 
1/4/2022DEF 
…..DEF 
1/1/2022GHI1/1/2022
1/2/2022GHI 
1/3/2022GHI 
……  

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Maha1 ,

     

    Here I suggest you to create a DimDate table and then create relationships based on it.

    DimDate =
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( Table1[Leave Date] ), -1 ) + 1,
        EOMONTH ( MAX ( Table1[Leave Date] ), 0 )
    ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Cal Year Month", FORMAT ( [Date], "YYYY MMM" )
)

    Relationship:

    For your request 1:

    Measures:

    No. of Employees on leave = CALCULATE(COUNT(Table1[Emp Name])) + 0
    Population = CALCULATE(COUNT(Table2[Emp Name]))
    % on leave = 
DIVIDE([No. of Employees on leave],[Population])

    Result is as below.

    For your request 2:

     

    Best Regards,
    Rico Zhou

     

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

     

    • Maha1's avatar
      Maha1
      Icon for Helper II rankHelper II
      3 years ago

      Greetings Anonymous ,

      I don't have the version to create Many to Many relationship. I'm trying to create the table using the below DAX formula, but I'm still facing issues. Where I cannot select the columns start and end dates of Table2 inside the CALENDAR function.

       

       

      New Table = GENERATE(
                      Table2,
                      CALENDAR( Table2[Start Date], Table2[End Date] )
                    ) ,