Filter a matrix based on selected value in another matrix

[voidbydefault]

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.

[voidbydefault]

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

[parry2k]

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

[voidbydefault]

Many thanks Parry, highly appreicated.

[parry2k]

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

[voidbydefault]

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

[parry2k]

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

[Ashish_Mathur]

Hi,

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

[voidbydefault]

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

[parry2k]

@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]

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

[parry2k]

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

[voidbydefault]

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