cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Anyway to enforce inner joins in my data model

Just starting with power BI.

 

What is the way to enforce an inner join in my data model.

I have a simple star model with 4 tables with one to many relationsship between them but no actual join seems to be enforced?

 

if I try to report on the set, it always reports on all records regardless if there is no related record in one of the tables?

 

Thanks in advance

1 ACCEPTED SOLUTION

When you add a measure which returns blank because of no matching records in the fact table then your visual is automatically filtered by only matching records (this behaviour can be changed)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

10 REPLIES 10
oliverpenney
Frequent Visitor

the data in reports will be the cross product of the tables involved

 

if you want to inner join, create a new query and join them in "edit Queries" > "merge queries" >"create as new..."

LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous

 

have you set up relationships between the tables?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

related.jpg

 

Yes 

 

can you describe one scenario of what is happening?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

Very simple actually, if I drag a field from one of the tables it gives me all records (1980), which it shouldn't since not all records have a related record (there are 1770 that can be inner joined)

 

 

yes that is the correct behaviour

the important thing is not have referential integrity issues where fact table rows have no reference into the related dimensions

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

In other words I always have to filter out the records that don't have a relation for every relation ?

That sounds very inefficient especialy when there are many relations involved.

 

So there is no equivelant to an inner join in power bi  relations ?

When you add a measure which returns blank because of no matching records in the fact table then your visual is automatically filtered by only matching records (this behaviour can be changed)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

OMG. This sounds like such a hack. I will give it a try but there has to be a setting somewhere.

Hello, 

Can you please explain this further. I am facing an issue currently where i have just two tables in a one to many relationship, Most of the columns i need are on the One side of the relationship but if i bring in any column from the Many Side the output on the table visual is an Inner Join of the two tables instead just showing blanks where there are no macthing errors. 

 

 

I could really use some help

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors