Crossfilter using a bridge table

[Huskyjimbo]

Hello,

I have a bridge table that shows for each Year&Month combo how many works days there are for each state we have offices.  The reason for this table is if there is snow in Colorado they may lose a workday but Florida would obviously be unaffected.

I'm having trouble trying to

1.) properly link this table because of many to many 

2.) write a measure that will show the work days for a month that is filterable by State or individual employee.

In the picture below it should say 22 or 21 not 2351

 

[v-juanli-msft · Accepted solution]

Hi @Huskyjimbo 

Please check my pbix below.

In my example, employee "a" has time off 15 hours in 2019/3,

he belongs to state 111 where has snow 2 weather day and it is in workday(Monday to Friday),

Basically, for 2019/3, there is 21 workdays (exclude the Saturday and Sunday), assume each day's working hours is 8 hours, 

then employee "a" should working 168(21*8) hours in 2019/3,

with the snow weather affeacted, his real working hours is 137 hours(168-2*8-15).

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You can get the workday count from the date table?

I am not sure about the need for month day count. Is that max type of leave per month.

[Huskyjimbo]

I can't get the work day count from the date table because a certain day may be a workday in  one state but not the other due to weather or a state holiday.