Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All ,
I have a scenario in Power BI and SSAAS 2016 Tabular model , if I am connecting startdate with date key from Date table then in power BI graph it is counting employee to be active only for date matching with start date and after that it is not counting this employee.
I am seeking for a solution where my employee should be visible on graph as active for all the dates between its start date and end date.
Could any one please suggest how do I join the start dates and end dates with Date dimension. Also, when I am showing date from date table in power BI graph this employee should count for each date between his start date and end date.
Thanks in advance !!!!
Hi @Kanika,
You could Cross Join Employee table and Date table. Then, filter the new crossjoin table with syntax: Date>=Start Date && Date<=End date
The DAX formula is similar to:
New Table = FILTER ( CROSSJOIN ( 'Date table', 'Employee table' ), 'Date table'[Date] >= 'Employee table'[Start date] && 'Date table'[Date] <= 'Employee table'[End date] )
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |