Forum Discussion
Helper VMeasure not being sliced by filter
I have a key named "teams" that has one column of team names. It has a one to many relationship with table masterall[team]. I used the teams table as the field in the slicer.
When I try to slice this measure...CountofActiveEmployee, it does not slice by team. Is there a way to modify this to be able to slice *This measure counts the number of active employees based on start and end dates and it works fine by itself but I need it to be able to be sliced.
DateTable1 is my date dimension
Count of Active Employee =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )
VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( MasterAll ),
FILTER (
ALL(MasterAll),
( MasterAll[Start Date] <= endOfPeriod
&& MasterAll[End Date] >= startOfPeriod
&& masterall[title]<>"non-billable"
&& masterall[title]<>"bench")
)
)
The likely problem is your ALL clause. You probably want to switch to ALLEXCEPT and include your Teams in your ALLEXCEPT.
2 Replies
- Greg_Deckler
Community Champion
The likely problem is your ALL clause. You probably want to switch to ALLEXCEPT and include your Teams in your ALLEXCEPT.
joshcomputer1Worked like a charm!
Count of Active Employee =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )
VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( MasterAll ),
FILTER (
ALLexcept(MasterAll, 'Key Teams'[Teams]),
MasterAll[Start Date] <= endOfPeriod
&& MasterAll[End Date] >= startOfPeriod
&& masterall[title]<>"non-billable"
&& masterall[title]<>"bench")
)