Slide 1
Slide 1 text
.
WEBINAR:
.
Relationships in PowerBI?
They can be “Complicated”
14 FEBRUARY
with Ivan Donev
Slide 2
Slide 2 text
Why do we
need
relationships
When you have multiple tables
and you need to correlate them
for analysis
When you want to optimize
models
When you want to optimize
performance
Slide 3
Slide 3 text
A sample for related tables
Slide 4
Slide 4 text
Relationships
in PowerBI
Cardinality
• Automatically detected
• 1:1 | 1:M | M:1 | M:N
• Duplicates are not allowed on the 1 side
Referential integrity issues
Relationship properties
• Active | Inactive
• Crossfilter direction
• Assume referential integrity
Slide 5
Slide 5 text
Weak and Strong relationships
• Strong relationships
• When the storage engine can determine the ONE side of a relationship
• Table expansion can take place
• From the Many to One
Slide 6
Slide 6 text
Table expansion
Slide 7
Slide 7 text
Table expansion (2)
Slide 8
Slide 8 text
Weak and Strong relationships (2)
• Weak relationships
• When cross-island
• When M:N
Slide 9
Slide 9 text
Functions to use around relationships
• USERELATIONSHIP() to handle multiple relationships between two
tables
• CROSSFILTER() to handle change the crossfilter direction
• RELATED() to lookup value from the 1 side of the relationship
• RELATEDTABLE to “lookup” set of values from a table on the Many
side of a relationship
Slide 10
Slide 10 text
Relationship and model ambiguity
Slide 11
Slide 11 text
Relationship and model ambiguity
Slide 12
Slide 12 text
Relationship and model ambiguity
• Precedence rules and path
resolution
• M:N including Weak relationships
• M:N
• Bi-directional in reverse (from the
* side)
• Delete or deactivate one of the
relationships
• Use SINGLE crossfilter direction
and change it with CROSSFILTER
function in measures
Slide 13
Slide 13 text
Resources
• https://www.sqlbi.com/articles/bidirectional-relationships-and-
ambiguity-in-dax/
• https://docs.microsoft.com/en-us/power-bi/desktop-relationships-
understand
• https://dax.tips/2019/11/28/clean-data-faster-reports/
Slide 14
Slide 14 text
What’s coming next
Event Date
Power Platform Bootcamp 15.02.2020
Global AI On Tour Bulgaria 2020 11.04.2020
Global Azure Bulgaria 2020 25.04.2020
SQL Server Discovery Day 2020 28.05.2020
AI and IoT Summit Bulgaria 2020 06.06.2020
SQL Saturday Plovdiv 2020 13.06.2020
Trainings @ sqlmasteracademy PowerBI Jumpstart - 17 March
Mastering PowerBI - 18-20 March
Business analysis and Data Exploration using PowerBI
and Power Query - 28-30 March