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
nitzer5
Frequent Visitor

Filter a table by Slicer value and return all rows based on primary value

 Hi there,

 

I have a problem where users want to be able to filter by their name and return all rows on a primary record (Work Order) even if they’re not listed on those rows, but they appear on some.

 

Eg.

Work Order

Responsible

Category

Baseline Start

1234567

Dan

IFC Elec

10/14/2021

1234567

Dan

IFR Elec

8/31/2021

1234567

Ken

IFC Civil

10/14/2021

1234567

Ken

IFR Civil

8/19/2021

 

Currently if the Slicer is set for “Responsible” and Dan searches by his name, the table will return just the first two rows. If he uses another slicer for “Work Order” and searches by 1234567 all four rows will be returned.
The users are adamant about searching by their name (instead of by Work Order) and seeing all rows for the Work Orders where they appear on, even if they aren’t part of that specific row.

 

Ie. Dan wants to use the “Responsible” slicer and search for his name and have all 4 rows show up for Work Order 1234567. Therefore, the desired outcome is the “Responsible” slicer filters Work Orders where Dan is mentioned and returns all rows for them and not just rows where his name shows up in the Responsible column.

 

I’m struggling to figure out a solution whether it’s using DAX measures for columns in the table or a bridge table or anything else. I’m open to all ideas.

 

Thank you

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@nitzer5 see attached solution and output below. Tweak it as you see fit.

 

2021-12-08_16-12-06.gif

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



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.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@nitzer5 see attached solution and output below. Tweak it as you see fit.

 

2021-12-08_16-12-06.gif

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



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.

Hello @parry2k 
Very nice job ! 
But the response time is too long
My table is 20 million rows in direct query.
Although I make slicer to reduce the amount of data to query, it's still long. about 10 minutes to display a table of 2 thousand rows. 
Could you help me to find another solution? or a way to improve the response time?
thank you so much
Best regards

This is exactly what I was looking for! Thank you so much for taking the time to explain and provide examples.

nitzer5
Frequent Visitor

@parry2k I'm pretty new to PBI, could you please elaborate on the steps and what measure needs to be created? Thank you!

parry2k
Super User
Super User

@nitzer5 you need to create a disconnected table for the user slicer and then create a measure to find the workorder associated with that user and list all the users. It is pretty straight forward once you have disconnected table for the slicer

 

 



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.