March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello everyone,
I haven't found the exact answer that I'm looking for, so I hope my question will be clear to understand.
I have two tables: X and Y. Let's say that both tables contain 4 columns that I need in 99% of my reports. Since I use custom queries all the time, I usually select the 4 columns from X and use a left join for the 4 columns from Y. Let's say that I have a report with 5 different queries, all of them use that left join, so 5 left joins in total.
Would it be better to just import table Y and use the built-in relationships instead of left joining in my SQL queries?
Would powerbi performance be better?
Thanks in advance. If my explanation is unclear, please let me know!
Solved! Go to Solution.
@Anonymous ,
The answer depends on how you want to shap your data model. Best Practice is a Fact Table (contains the items that you want to summate, count, etc), and a few Dimension tables (ones that will help you filter the fact table).
If your PBI data model requires the two SQL tables to be in one table, then join on loading via the SQL script. Or if you access to the SQL DB, create a View that joins them, then you can just load the view into PBI.
Also, as @v-diye-msft said, the Relationships in PBI act in certain ways. This may not be in the way you need for your model, as the relationships are filter relationships not joins.
hope that helps.
@Anonymous ,
The answer depends on how you want to shap your data model. Best Practice is a Fact Table (contains the items that you want to summate, count, etc), and a few Dimension tables (ones that will help you filter the fact table).
If your PBI data model requires the two SQL tables to be in one table, then join on loading via the SQL script. Or if you access to the SQL DB, create a View that joins them, then you can just load the view into PBI.
Also, as @v-diye-msft said, the Relationships in PBI act in certain ways. This may not be in the way you need for your model, as the relationships are filter relationships not joins.
hope that helps.
Thank you both so much for your answers. I thought I understood what relationships are for - linking tables. What I completely missed was that relationships indeed filter results, not join them.
So the two methods are somewhat the same, yet the results can be very different.
Hi @Anonymous
When we load two or more tables at the same time, Power BI desktop will look at column names in the tables we are querying to determine if there are any potential relationships. If there are, those relationships are created automatically.
From Cardinality and Result we can get:
many : many -> Inner
one : one -> Full Outer
one : many -> RightOuter
many : one -> LeftOuter
if the relationships set by SQL DB is the same as Power BI detect, then you needn't modify them. While the logic is different, you can manage relationships follow this article based on your needs. See: Create and manage relationships in Power BI Desktop.
Here's similar thread could also be your reference:
https://community.powerbi.com/t5/Desktop/Relationships/m-p/233398
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
128 | |
90 | |
75 | |
56 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |