Returning filtered out results in a separate visual Table

[Beavertron]

I have a table which contains a list of employees and when they were active in the field. Simple Branch + Unique Key merged field, then a date slicer filters this to show who worked in the field during any selectable period. How can also show a second table listing the employees that were inactive in the field during this period. I am able to create a measure to give the number, but I would like to list the employees in a table. 

I have tried measures and duplicate tables then creating a merged query for those results that don't show in the main table (left ansi) but I got this wrong on how it needs to work. It needs to be dynamic with the date and branch slicers. I hope someone can advise, basically show me the opposite results of another table thats filtered.

Thank you

[Anonymous · Accepted solution]

Hi @Beavertron 

I think you want to dynamically get active and inactive employee lists through Date slicer.

Now your problem is that you can't get a list by measure.

Measure will show you aggregate values, so you can't get a list by measure directly.

However you can build a measure to filter your employee list to achieve your goal.

My Sample:

Firstly we need to build a Date table for slicer.

Date = 
CALENDAR(MIN('Table'[ActiveDate]),MAX('Table'[ActiveDate]))

Measure:

ActiveFilter = 
VAR _Period = VALUES('Date'[Date])
Return
IF(MAX('Table'[ActiveDate]) in _Period,1,0)

Build two emplotyee lists and add this measure into the filter field of two employee lists.

Then set this measure to show items when value =1 in active employee list, set this measure to show items when value =0 in inactive employee list.

You see active will show all employee and inactive will show blank by default.

If we set date between 2021/1/1 to 2021/1/24, result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Beavertron]

Hi, thank you so much. Being quite new to powerBI I didn't realist that measures could be used in table filters this way. This is incredibly power and handy to know. Many thanks

[parry2k]

@Beavertron Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490