Webinar - Relationships in PowerBI - they can be complicated

Webinar - Relationships in PowerBI - they can be complicated

Transcript

1. . WEBINAR: . Relationships in PowerBI? They can be “Complicated” 14 FEBRUARY with Ivan Donev
2. 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
3. A sample for related tables
4. 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
5. 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
6. Table expansion
7. Table expansion (2)
8. Weak and Strong relationships (2) • Weak relationships • When cross-island • When M:N
9. 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
10. Relationship and model ambiguity
11. Relationship and model ambiguity
12. 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
13. 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/
14. 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