Crossfilter through three different tables.

[ThomasWeppler]

Hi power BI community

I want to make a report that see how much time diffrent people are available in diffrent month, to better plan capacity.
To achieve this I use three different tables.

Table one is a called User
it contains the columns [name], [user_id], [Monday], [Tuesday], [Wnedsday], [Thursday], [Friday]
[user_id] is a number that connects it to the other tables.
The last 5 columns monday - Friday contains the number of hours as a flow the user works that day.

I have a date table called Future
It contains all future days for the next three years

It has a column called [date] that contains the dates and a column that is called month_year that group the date together in the month and year the belong in like this "nov-26" for all dates in November 2026

the final table is called WorkPlanEntry

It contains a [date] that connects it to the future table

a [user_id] that connects it to the User table and a [Title] that shows why a user will not work a specific day. It could say "Holliday"

I also have a measure which calculate all the hours the users should work. it looks like this

Time =

var _new_date = CALCULATETABLE(Future,'Future'[Date])

var _countmonday = calculate(COUNT(Future[Date]), Future[Weekday] = 2)

var _counttuesday = calculate(COUNT(Future[Date]), Future[Weekday] = 3)

var _countwednesday = calculate(COUNT(Future[Date]), Future[Weekday] = 4)

var _countthursday = calculate(COUNT(Future[Date]), Future[Weekday] = 5)

var _countFriday = calculate(COUNT(Future[Date]), Fremtid[Weekday] = 6)

var _totalmonday = SUM('User'[Monday]) * _countmonday

var _totaltuesday = SUM('User'[Tuesday]) * _counttuesday

var _totalwednesday = SUM('User'[Wends]) * _countwednesday

var _totalthursday = SUM('User'[Thursday]) * _countthursday

var _totalFriday = SUM('User'[Friday]) * _countFriday

var _total = _totalmonday + _totaltuesday + _totalwednesday + _totalthursday + _totalFriday

return

_total

Here is my problem. I want to exlude all the Hollidays and sick days from the WorkPlanEntry table.

Can anyone help me do this?
Alle help will be greatly appreciated.

[rohit1991 · Accepted solution]

To exclude holidays and sick days from the WorkPlanEntry table, you can modify your measure to filter out entries with Title values indicating holidays or sick days. Here's how you can adjust your Time measure:

Revised DAX Measure:

Time =
VAR _filteredDates =
    CALCULATETABLE (
        'Future',
        NOT (
            CONTAINSSTRING ( RELATED ( WorkPlanEntry[Title] ), "Holiday" )
                || CONTAINSSTRING ( RELATED ( WorkPlanEntry[Title] ), "Sick" )
        )
    )
VAR _countMonday =
    CALCULATE ( COUNTROWS ( _filteredDates ), WEEKDAY ( Future[Date], 2 ) = 1 )
VAR _countTuesday =
    CALCULATE ( COUNTROWS ( _filteredDates ), WEEKDAY ( Future[Date], 2 ) = 2 ) 
VAR _countWednesday =
    CALCULATE ( COUNTROWS ( _filteredDates ), WEEKDAY ( Future[Date], 2 ) = 3 ) 
VAR _countThursday =
    CALCULATE ( COUNTROWS ( _filteredDates ), WEEKDAY ( Future[Date], 2 ) = 4 ) 
VAR _countFriday =
    CALCULATE ( COUNTROWS ( _filteredDates ), WEEKDAY ( Future[Date], 2 ) = 5 ) 
VAR _totalMonday =
    SUM ( 'User'[Monday] ) * _countMonday
VAR _totalTuesday =
    SUM ( 'User'[Tuesday] ) * _countTuesday
VAR _totalWednesday =
    SUM ( 'User'[Wendsday] ) * _countWednesday
VAR _totalThursday =
    SUM ( 'User'[Thursday] ) * _countThursday
VAR _totalFriday =
    SUM ( 'User'[Friday] ) * _countFriday
RETURN
    _totalMonday + _totalTuesday + _totalWednesday + _totalThursday + _totalFriday

[ThomasWeppler]

Hi @rohit1991 
Thanks for the answer. Your suggestion inspired me to use Related Tables and that solved my problem in the end. 🙂

[Thejeswar]

Hi @ThomasWeppler ,

If this Holiday and Sick days data are not required for the entire report, then it is better that you filter these records in the Power Query.

In case if it is required and that you don't need it for this measure, then consider changing the DAX formula as follows.

Note: I am just making an assumption with this measure, not very sure this will work, but may be worth giving a try

Time =

var _new_date = CALCULATETABLE(Future,'Future'[Date])

var _countmonday = calculate(COUNT(Future[Date]), Future[Weekday] = 2, OR(Future[Title] != "Holiday", Future[Title] != "Sick"))

var _counttuesday = calculate(COUNT(Future[Date]), Future[Weekday] = 3, OR(Future[Title] != "Holiday", Future[Title] != "Sick"))

var _countwednesday = calculate(COUNT(Future[Date]), Future[Weekday] = 4, OR(Future[Title] != "Holiday", Future[Title] != "Sick"))

var _countthursday = calculate(COUNT(Future[Date]), Future[Weekday] = 5, OR(Future[Title] != "Holiday", Future[Title] != "Sick"))

var _countFriday = calculate(COUNT(Future[Date]), Fremtid[Weekday] = 6, OR(Future[Title] != "Holiday", Future[Title] != "Sick"))

var _totalmonday = SUM('User'[Monday]) * _countmonday

var _totaltuesday = SUM('User'[Tuesday]) * _counttuesday

var _totalwednesday = SUM('User'[Wends]) * _countwednesday

var _totalthursday = SUM('User'[Thursday]) * _countthursday

var _totalFriday = SUM('User'[Friday]) * _countFriday

var _total = _totalmonday + _totaltuesday + _totalwednesday + _totalthursday + _totalFriday

return

_total

[ThomasWeppler]

Thanks for the suggestion.
I think it might be a good idea to focus on the DAX.

However when I put the DAX