Forum Discussion
ThomasWeppler
1 year agoImpactful Individual
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...
- 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
rohit1991
1 year agoSuper User
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- ThomasWeppler1 year agoImpactful Individual
Hi rohit1991
Thanks for the answer. Your suggestion inspired me to use Related Tables and that solved my problem in the end. 🙂