Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Beavertron
Frequent Visitor

Returning filtered out results in a separate visual Table

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

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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:

1.png

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.

2.png

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

3.png

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. 

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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:

1.png

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.

2.png

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

3.png

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. 

 

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
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.