Merge Tables for Custom Slicer?

[sarah2]

I'm probably just over thinking this, but I think I want a calculated table to get me a custom slicer. Here's the situation:

My main data table has a lot of names - current and past employees. They all have lots of rows of data and in some reports I want all of it, but in others I just want to see the current employees. I could filter the page and manually put the checkmark for current employees in the filtering, but I'm hoping theres a way to do it more hands free with a slicer. We also have some turnover so I'd like it to be as flexible as possible as employment status changes. Here are the sample tables of information I have:

"All Table" being my main data set of information (lots of rows, repeating names). I also have a "current table" that I can use which is updated to only contain current employee names. There are some employees that do not have data yet (not sure if that will impact the solution, I don't really need them in this until they have data but they are on that list). 

I would like a slicer where I could select from two options "current" and/or "past" employees and it would filter a visual of All Table data. I'm not sure if the best way to do that is to get a table like "result" or if there's another way (I think a merged table wouldn't be flexible with changing data as employees start and leave but I could be wrong?)

Thank you!

[tharunkumarRTK · Accepted solution]

You can do a left outer join (merge) for ALL TABLE with current table. And after that you can replace the null vlaues in the result with Past and other values with Current. Not sure why you think merge is not flexible. 

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[sarah2]

That seems to be working great thank you! I can change the "null" to "Past" using the Replace Values, but I'm not sure how to change the other values to "current"

[tharunkumarRTK]

@sarah2  

There are many ways to do that, one way that you can follow is adding a conditional column 

if [ColumnName] <> "Past" then "current" else "Past"

Copy the above code in custom column field.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun