Can I filter two similar columns from two different tables without a relationship?

[Anonymous]

Hello there, by similar, I mean two different columns share the same values, and can work as foreign keys, only in this instance they can't. It might sound a bit bizarre. But currently I have 12 tables in this project. However, I can't build another link between these two tables because it will introduce ambiguity (I have many duo filters which I need). 

Here is the model diagram shown below:

Everything here works fine and works how it's supposed to despite one limitation. I want to match the Deals (CRM) table with the Projects (Books) table. The two highlighted columns are what I want to match, they share the same name. So for example, a Project Name can be called "Table & Chairs", and Project 1 would have have the exact same value. From there, there is a link. 

I want to be able to filter a Project name through a Deal in my report. Without changing any of my relationships above, because I need them to work as they are. 

Would there be any way to achieve this? 

[v-shex-msft]

Hi @Anonymous,

Power bi data models(AS tabular) do not need to build too many relationship keys, current they only required one major relationship for the data analysis between different tables.

I'd like to suggest you modify these relationship mappings to use star schema to link tables with bridges.

Relationships in analysis services tabular models 

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Regards,
Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft thanks for the response but I don't know how I could remodel this into a Star Schema. The relationships have to be the way they currently are for the filters to work correctly. Otherwise the filters will not work. 

[amitchandak]

@Anonymous , You need to have a common dimension table with a distinct project id. You can join with both tables and filter of you can use filter values of this table.

If the project table is not joined

calculate(Count(Table1[Value]), filter(Table1, Table1[project] in values(Project[Project]))

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

refer

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[Anonymous]

Hi @amitchandak could you please explain this measure here "calculate(Count(Table1[Value]), filter(Table1, Table1[project] in values(Project[Project]))"

I'm confused on the table1 you're using. I currently want table Deal (CRM) column Project 1 to filter table Projects (Books) column Project Name. 

Would that work?