Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.