Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Icon for Impactful Individual rankImpactful Individual
1 year ago
Solved

Crossfilter through three different tables.

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.

 

 

 

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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

4 Replies

  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      ThomasWeppler
      Icon for Impactful Individual rankImpactful Individual
      1 year ago

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

      However when I put the DAX 

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      ThomasWeppler
      Icon for Impactful Individual rankImpactful Individual
      1 year ago

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