Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Impactful 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. Tab...
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
Thejeswar's avatar
Thejeswar
Super 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