Forum Discussion
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
- parry2k
Super User
voidbydefault I have posted the solution with the link to the pbix file. Take a look.
- voidbydefault
Helper I
Many thanks Parry, highly appreicated.
- parry2k
Super User
voidbydefault is 2nd matrix also based on the table that is used in the 1st matrix?
- voidbydefault
Helper I
Yes. It's based on the same data table.
- parry2k
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.
- voidbydefault
Helper I
Thanks parry2k . I will give it a try, so far all measures I tried have filed to yield results.
- Ashish_Mathur
Super User
Hi,
I have solved a similar problem in the attached PBI file.
- voidbydefault
Helper I
Thanks Ashish_Mathur for your efforts, but my question goes beyond the use of slicers.
- parry2k
Super User
voidbydefault as I mentioned will not work with a single table, you need a disconnected table to make it work.
- parry2k
Super User
voidbydefault try it, and if not share a sample pbix file and I will get back to you.
- voidbydefault
Helper I
I've sent you a PM parry2k . Thanks.
- voidbydefault
Helper I
^bump -- if anyone has another idea. Thanks.