filter based on similar items in dimensional table

[Anonymous]

Dear all,

For simplicity, I have a dimension table (table1) with a key and two dimensions. Also have a fact table (table2) with the same key and linked one-to-many to teh fact table.  Also have a slicer with the key (ID) of the dimension table as values. When I select a value, I would like to filter the fact table for all ID's that have the same dimensions as the selected value in the slicer, so in my case below id =1,3,4. Anyone any clues?

regards

Table1
ID	Dim1	Dim2
1	A	C
2	A	D
3	A	C
4	A	C
5	B	D
6	B	D
7	B	C
8	B	D

Table2
ID	City
1	New York
3	San Francisco
2	Boston
2	Atlanta
4	Miami
1	Seatle
5	Houston
7	Washington

Outcome
ID	City
1	New York
3	San Francisco
4	Miami
1	Seatle

[v-tangjie-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1)  My test data is the same as yours.

(2) We can create a table and a measure.

Table 3 = 'Table1'

Measure = 
VAR _dim1 = SELECTEDVALUE('Table 3'[Dim1])
VAR _dim2 = SELECTEDVALUE('Table 3'[Dim2])
VAR _table = CALCULATETABLE(VALUES('Table1'[ID]),FILTER(ALLSELECTED('Table1'),'Table1'[Dim1]=_dim1 && 'Table1'[Dim2] =_dim2))
VAR _filter = IF(SELECTEDVALUE('Table2'[ID]) IN _table,1)
RETURN
_filter

(3) Filter visuals with measure and then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[onurbmiguel_]

Hello 

I have try like this: 

create a slicer: 

ands i return this output:

Is this what you want? 

Best regards

Bruno Costa | Solution Supplier

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

[Anonymous]

not exactly as i just want to slicer on ID =1 and then filter on al similar id's with same characteristics in table1. For small example likt this your sollution would work, but this is a simplification of a much larger case with thousands of row