List of Employees not in a range of dates

[MarceloPavas_27]

Hi everyone,

I have a list os Employees and Dates and I need to list all employees that are not in a certain period. For example:
We have the [Employee] column and the [Year] column
And we have a filter equal to 2022
So, I need some way to show a list of all employees that has no 2022 value on the data table.

But if I change the Year on the filter pane to 2021 the list needs to comply with the filter.

Can anyone please help me?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

One of ways to achieve this is to have disconnected Year_table, so once a year is selected from a slicer, the table visualization can show year numbers that is not equal to the year number from the slicer.

Year measure: =
VAR maxyearslicer =
MAX ( 'Year'[Year] )
VAR slicerselect =
SELECTEDVALUE ( 'Year'[Year], maxyearslicer )
RETURN
IF (
HASONEVALUE ( Employee[Employee] ),
CALCULATE ( MAX ( Employee[Year] ), Employee[Year] <> slicerselect )
)

[MarceloPavas_27]

Thanks @Jihwan_Kim this helps me a lot to find a way to work around my problem.

Best regards!

[amitchandak]

@MarceloPavas_27 , Assume you have count of employee measures

_cnt = count(Table[Employee])

or

_cnt =
var _max = maxx(allselected(Date), Date[Year])
return
calculate(count(Table[Employee]), filter(Table, Table[Year] = _max))

Then create a measure to find who is not there in selected range, prefer Employee dimension 

Measure =
countx(values(Employee[Employee]), if(isblank([_Cnt]), [Employee], blank()))