Dynamically Filter a table based on the value of another table

[fellaz4sho]

Hello 

I am having a huge issue with using a table A to filter my original table B. I have been banging my head against the wall for weeks now. I have import both tables from the database. If i go into my query and hard code my filters in, it worked but that is not what i want. 

Example: 

Select * from dbo_data

where id like dog

This query will work just fine but i dont want to hard code that "dog" into my query. I want to bring both tables seperately into my query editor. Query 1(table A) and Query 2(table B) and use table A as my filter. So when i go into my data model, I have both tables and i can filter Table B with table A. I tried the Dynamic parameter from the "guy in the cube" and that didnt help me since i am using a database vs excel but, it did work when i used excel. 

I also tried filtering table B and changing the filtered row value to " table A" and that didnt work either. Please, help me. This is very important for the project i am working on. Thank you. 

Table A		Table B
ID		ID	Name
1		1	Fish
2		2	Dog
3		3	Cat
4		4	Goat
5		5	Shark
6		6	Turkey

[Anonymous]

2 questions for you:

1. Is Table A supposed to be a Dimension /  Lookup table?
   1. If Yes, then does the ID column have unique values for every row...no duplicates?
2. Is Table B supposed to be a Fact table?

If yes to all of these questions, you should create a "Many-to-One" relationship between Table B (many) and Table A (one) via the relationship view page.

Then you can use 'Table A' [ID column] in a slicer, table, etc.  and it will properly filter values in Table B.

You may want to read up on Data modeling and relationships, as these are a core and fundamental feature of Power BI.  Suggest looking at some of the blog articles on www.powerpivotpro.com and http://exceleratorbi.com.au/

[fellaz4sho]

Thank you Chris for your prompt reply. I have a clear understanding of relationships but that is not what I am looking for. If for just slicer, i have them related one to many table A being my lookup and table B being my fact and i can slice Table B with A. What i am looking for is using Table A in place of a where clause for Table B so to speak. So I want Table A to basically be my where clause so that, i can use it with the measures i created in Table B in my data model. 

In the Advanced editor below, is it possible to take my #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([ID] = 1)) and change the ID value to be my Table A? By doing this, Table B will have to rely on Table A for input.  Thank you

let
Source =

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFSAGIwwy8xN1UpVidayRAqCqLdMoszwIJGUEEQ7ZKfDhYzhoqBaOfEErCYCVQMRLvnQwVNoYIgOjgjsSgbLGoGFQXRIaVF2amVSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Table A" = _t, #" " = _t, #"Table B" = _t, #" .1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Table A", type text}, {" ", type text}, {"Table B", type text}, {" .1", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Table A", " "}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Name", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([ID] = 1))
in
#"Filtered Rows"