Date dimension table and Employee fact table in SSAS tabular 2016 and power BI

[Kanika]

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 !!!!

[v-yulgu-msft]

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

Then, establish a one to many relationship from 'Date table' to 'New table' based on [Date] column.

Regards,

Yuliana Gu

[Kanika]

Thanks Yuliana,

Is there a way to achive this without creating any additional physical table with Cross join because I have this scenario at multiple places that will impact my dictionary size.

Ultimately, at the end the need is to drag dates on one axis and active user count on another axis.

Regards,
Kanika Soni