June 20, 2023 asghars
- When working with multiple tables, it is highly likely that you will need to perform analysis using data from all of them.
- Relationships are rules that define how two tables can be associated.
- Relationships connect one table column to another table’s column.
- Power BI Desktop can automatically detect relationships between tables by matching columns with the same name and datatype.
- Relationships can be defined as:
- one-to-many (1:*)
- In one-to-many relationship, a single record in one table can be linked to one or more records in another table.
- Often the Lookup table contains only a single record, while the Data table contains one or more records.
- The column of Lookup table that is involved in relationship will always contain unique values.
- one-to-one (1:1)
- In one-to-one relationship, a single record in one table can be linked to only one record in another table.
- Both columns in a one-to-one relationship have unique values.
- Relationship between tables allow
- To apply star schema
- Cross Filter direction
- By default, in a one-to-many relationship, filters can flow downwards in a single direction, specifically from the "one" side to the "many" side. This means that only column from Lookup table can filter data in the Data table.
- The default unidirectional behavior can be modified to allow for both or bi-directional filtering, enabling columns from both the Lookup and Data tables to filter the data.
- The drawback of enabling bi-directional filtering is that it can potentially slow down reports.
- For one-to-one relationship filter flow direction is always from both tables.
- To take advantage of different DAX functions like
- RELATED (columnName) function fetch the value of the specified column from "one" side of the relationship.
- RELATEDTABLE (tableName) function retrieves values in table format from the specified table located on the "many" side of the relationship.
- USERELATIONSHIP (column1, column2)
- This function only enables the indicated relationship for the duration of the calculation by specifying the primary and foreign columns as parameters.
- Note; a pair of tables can have multiple defined relationships, but only one of them can be designated as the active relationship at a given time.
- CROSSFILTER (leftColumn, rightColumn, filterDirection) function specifies cross filtering direction to be used in the evaluation of an expression.
0 comments:
Post a Comment