cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KyleAdam
Helper IV
Helper IV

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

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:

KyleAdam_0-1631891537414.png

 

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. 

KyleAdam_1-1631891769734.png

 

Would there be any way to achieve this? 

 



 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @KyleAdam,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Super User
Super User

@KyleAdam , 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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!