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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
voidbydefault
Frequent Visitor

Filter a matrix based on selected value in another matrix

Hi,

 

Requirement:
I have a table called [data_tickers], amongst other columns, it has columns called  [company] and [industry] and presented in a matrix (let's call it matrix 1). I want to show all peer companies in matrix 2 based on the [industry] of selected value (i.e. company) in martrix 1. For example:

 

Company | Industry

A | IT

B | Food processing

C | IT

... etc.

 

If company B is selected then matrix 2 should show all companies from the group "food processing".

 

Note:

My requirement is different than using slicers to filter both matrices. I want to filter matrix 2 results based on selected value in matrix 1.

 

I have tried using:

 

 

 

 

 

VAR selected_industry = SELECTEDVALUE(data_tickers[industry])
RETURN

CALCULATE(
   SUMMARIZE(data_tickers, data_tickers[industry]),
   FILTER(data_tickers,data_tickers[industry] = selected_industry)
)

 

 

 

 

 

 

Also tried this:

 

 

 

 

 

VAR selected_industry = SELECTEDVALUE(data_tickers[industry])
RETURN

CALCULATETABLE(
   FILTER(
      ALL(data_tickers),
   data_tickers[industry] = selected_industr)),
data_tickers[shortName]
)

 

 

 

 

And a few other similar things, but all attempts are failing for one or other reason.

 

Any ideas?

 

Thanks.

 

12 REPLIES 12
voidbydefault
Frequent Visitor

^bump -- if anyone has another idea. Thanks.

parry2k
Super User
Super User

@voidbydefault I have posted the solution with the link to the pbix file. Take a look.



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.

Many thanks Parry, highly appreicated.

parry2k
Super User
Super User

@voidbydefault try it, and if not share a sample pbix file and I will get back to you.



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.

I've sent you a PM @parry2k . Thanks.

parry2k
Super User
Super User

@voidbydefault as I mentioned will not work with a single table, you need a disconnected table to make it work.



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.

Ashish_Mathur
Super User
Super User

Hi,

I have solved a similar problem in the attached PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @Ashish_Mathur for your efforts, but my question goes beyond the use of slicers.

parry2k
Super User
Super User

@voidbydefault Unfortunately with the current single table it will not work, you need to have another table (aka dimension table) for industry to be used to achieve the desired result.



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.

Thanks @parry2k . I will give it a try, so far all measures I tried have filed to yield results.

parry2k
Super User
Super User

@voidbydefault is 2nd matrix also based on the table that is used in the 1st matrix?



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.

Yes. It's based on the same data table.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.