DAX - Table Relationships Flashcards
What is a Fact table?
A table that keeps numeric data that might be aggregated in the reporting visualizations.
What are dimensions in Power BI?
A table that keeps descriptive information that can slice and dice the data of the fact table.
What is a role-playing dimension?
Instances of the same dimension used multiple times in a data model
Why use role-playing dimensions?
To analyze data from different viewpoints without duplicating data tables.
Example of role-playing dimensions
A date dimension where order date, shipped date and delivery date each use the same date table. The Date Table is a role-playing dimension.
What is an active relationship?
The relationship between two tables that is currently active.
What is an inactive relationship?
A valid relationship not being actively used in the current analysis.
How can you tell which relationships are active and which are inactive?
- An active relationship is represented by a solid line
- An inactive relationship is represented by a dotted line
Example of multiple relationships
The three relationships all connect from one field in the Date table to the Order Date, Shipped Date and Delivery Date of the Sales table
By default, which relationship is made active if there is more than one?
The first relationship created is automatically made active. Any subsequent relationships will be made inactive.
USERELATIONSHIP syntax
USERELATIONSHIP(
TableName1[ColumnName1],
TableName2[ColumnName2]
)
Note that the column order does not matter. You just need to indicate which columns you want to join.
This function does not return a value, it simply alters the filter context of a calculation.
How does USERELATIONSHIP work?
- Forces the inactive relationship you specify to be used instead of the currently active relationship.
- It can be used multiple times within a single calculate function to switch multple relationships.
- Must exist in the data model
Where is the USERELATIONSHIP function used?
Within the CALCULATE function. If you try to use it anywhere else you will get an error.
Example of USERELATIONSHIP
Total Sales (Shipping Date) =
CALCULATE (
SUM(Sales[Sa1es Amount]), //Expression
USERELATIONSHIP( //Filter
Sales[Shipping Date], //Table1
Date[Date]) //Table2
)
- The CALCULATE function is used to alter the filter context of the entire measure.
- Then a temporary relationship based on the shipping date column is made active by using USERELATIONSHIP
What is an advantage of using USERELATIONSHIP?
It enables you to perform analysis using different relationships available between the related tables without affecting the overall structure of the data model.
