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 Date||Emp Name|
Table2: (shows all active employees whether he/she had leave date or not)
|Cal Year Month||Emp Name|
Expected Results in the report:
|Day||No. of Employees on leave||Population||% on leave|
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?
|Date||Emp Name||Leave Date|
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" ) )
For your request 1:
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:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greetings @v-rzhou-msft ,
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] ) ) ,
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.