I was inspired by Vahid Doustimajd's post on using the new INFO DAX functions to surface information about relationships to create my own version. Vahid's excellent post is available at https://www.linkedin.com/pulse/using-info-dax-functions-clear-table-relationship-power-doustimajd-ytfdc%3FtrackingId=%252BTkoOlYuTr2dDx5h1wuYfA%253D%253D/?trackingId=%2BTkoOlYuTr2dDx5h1wuYfA%3D%3D. I wanted to create my own as I tend to like the relationships explained as To/From instead of From/To (as Power BI tends to switch the MANY side to always being the FROM table and I like to explain relationships with ONE side first) and to have the properties explained in sentences. This will work in DAX query view of Power BI Desktop and also external tools such as DAX studio or Tabular Editor. And of course, in DAX.do.
See it now in DAX.do: https://dax.do/pVRGYYcUq7Xpa3/
Here is just the DAX query of the relationships information (it's a bit long!):
DEFINE
VAR _Table =
SELECTCOLUMNS(
INFO.TABLES(),
"TableID", [ID],
"TableName", [Name]
)
VAR _Col =
SELECTCOLUMNS(
INFO.COLUMNS(),
"ColumnID", [ID],
"ColumnName",
IF(
ISBLANK([ExplicitName]),
[InferredName],
[ExplicitName]
)
)
// https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.relationship
VAR _RelFrom =
SELECTCOLUMNS(
INFO.RELATIONSHIPS(),
[ID],
[FromCardinality],
// https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.objectstate
"State",
SWITCH(
[State],
1, "Ready",
3, "No Data",
4, "Calculation Needed",
5, "Semantic Error",
6, "Evaluation Error",
7, "Dependency Error",
8, "Incomplete",
9, "Syntax Error",
10, "Force Calculation Needed",
"Unknown"
),
[IsActive],
[RelyOnReferentialIntegrity],
[CrossFilteringBehavior],
[SecurityFilteringBehavior],
"ColumnID", [FromColumnID],
"TableID", [FromTableID]
)
VAR _RelTo =
SELECTCOLUMNS(
INFO.RELATIONSHIPS(),
[ID],
[ToCardinality],
[State],
[CrossFilteringBehavior],
"ColumnID", [ToColumnID],
"TableID", [ToTableID]
)
VAR _FromRel =
SELECTCOLUMNS(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
_RelFrom,
_Table
),
_Col
),
[ID],
[State],
[IsActive],
[RelyOnReferentialIntegrity],
"From Table", [TableName],
"From Column", [ColumnName],
"Cross Filter Behavior",
SWITCH(
[CrossFilteringBehavior],
1, "One",
2, "Both",
"Something else"
),
"Security Filter Behavior",
SWITCH(
[SecurityFilteringBehavior],
1, "One",
2, "Both",
3, "None",
"Something else"
),
"From Cardinality",
SWITCH(
[FromCardinality],
1, "One",
2, "Many",
"Something else"
)
)
VAR _ToRel =
SELECTCOLUMNS(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
_RelTo,
_Table
),
_Col
),
[ID],
"To Table", [TableName],
"To Column", [ColumnName],
"Cross Filter Behavior (To)",
SWITCH(
[CrossFilteringBehavior],
1, "One",
2, "Both",
"Something else"
),
"To Cardinality",
SWITCH(
[ToCardinality],
1, "One",
2, "Many",
"Something else"
)
)
VAR _result =
SELECTCOLUMNS(
NATURALLEFTOUTERJOIN(
_FromRel,
_ToRel
),
"Relationship (From/To)",
"'" & [From Table] & "'[" & [From Column] & "] " & SWITCH(
[From Cardinality],
"One", "1",
"Many", "*",
"?"
) & SWITCH(
[Cross Filter Behavior],
"One", "[<-]",
"Both", "[<>]",
"[?]"
) & SWITCH(
[To Cardinality],
"One", "1",
"Many", "*",
"?"
) & " '" & [To Table] & "'[" & [To Column] & "]"
& IF([IsActive],"", " (Inactive)"),
"Relationship (To/From)",
"'" & [To Table] & "'[" & [To Column] & "] " & SWITCH(
[To Cardinality],
"One", "1",
"Many", "*",
"?"
) & SWITCH(
[Cross Filter Behavior],
"One", "[->]",
"Both", "[<>]",
"[?]"
) & SWITCH(
[From Cardinality],
"One", "1",
"Many", "*",
"?"
) & " '" & [From Table] & "'[" & [From Column] & "]"
& IF([IsActive],"", " (Inactive)"),
[From Table],
[From Column],
[To Table],
[To Column],
"Is active",
IF([IsActive],"Active", "Inactive: USERELATIONSHIP required in a DAX formula to activate."),
"Cardinality (From/To)",
// Simpler version
// [From Cardinality] & "/" & [To Cardinality],
UPPER( [From Cardinality]) & " row" & if([From Cardinality] = "One", "","s") & " in " & "'" & [From Table] & "'[" & [From Column] & "]"
& " matches to "
& UPPER([To Cardinality]) & " row" & if([To Cardinality] = "One", "","s") & " in " & [To Table] & "'[" & [To Column] & "]",
"Cardinality (To/From)",
UPPER( [To Cardinality]) & " row" & if([To Cardinality] = "One", "","s") & " in " & "'" & [To Table] & "'[" & [To Column] & "]"
& " matches to "
& UPPER([From Cardinality]) & " row" & if([From Cardinality] = "One", "","s") & " in " & [From Table] & "'[" & [From Column] & "]",
// https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.relationship.crossfilteringbehavior
// https://learn.microsoft.com/power-bi/transform-model/desktop-bidirectional-filtering
"Cross Filter Behavior",
SWITCH(
[Cross Filter Behavior],
"One", "One direction only: '" & [To Table] & "'[" & [To Column] & "] row filter/slicer selections (direct and indirect) will also filter matching rows in " & "'" & [From Table] & "'[" & [From Column] & "], but not vice versa.",
"Both", "Both directions: Filter/slicer selections from either table will also filter the other.",
"Something else"
),
// https://learn.microsoft.com/power-bi/enterprise/service-admin-rls
// https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.securityfilteringbehavior
"Security Filter Behavior",
SWITCH(
[Security Filter Behavior],
"One", "One direction: '" & [To Table] & "'[" & [To Column] & "] row level security (RLS) filters (direct and indirect) will also filter matching rows in " & "'" & [From Table] & "'[" & [From Column] & "], but not vice versa.",
"Both", "Both directions: Row level security (RLS) will apply from either table to the other.",
"None", "None: Row level security (RLS) will not filter rows through this relationship",
"Something else"
),
// https://learn.microsoft.com/power-bi/connect-data/desktop-assume-referential-integrity
"DirectQuery SQL join type",
IF([RelyOnReferentialIntegrity], "INNER join: non-matching rows excluded.", "OUTER join: non-matching rows included in a (Blank) group.")
)
EVALUATE
_Result And you can see it on the DAX.do example model:
And for Power BI Desktop, follow along with the Employee Hiring and History PBIX file.
Open in Power BI Desktop and navigate to DAX query view.
Don't see DAX query view? Go to File > Options and Settings > Options > Preview Features > and click the check mark next to DAX query view.
Click the green + to create a new query tab. Then paste in the DAX query from above and click Run.
I've included some helpful links about some of the relationship properties, such as Security Filter Behavior and DirectQuery SQL join type (I made that name up, it's also known as Assume referential integrity and Rely on Referential Integrity). These are more nuanced relationship properties that impact how the semantic model applies Row-Level Security (RLS) and for DirectQuery or Direct Lake storage modes, respectively.
The relationship between 'Employee' table and 'Date' table is by the [Date] column and when looking at it in Model view I can see the following information:
The top table listed is the "From" table, in this case Employee, and the bottom table is the "To" table, in this case Date. There is a many to one relationship and the arrow points from Date to Employee, indicating Date table will filter rows in Employee table.
And this is shown in the DAX query as this row:
This relationship is summarized as 'Date'[Date] 1[->]* 'Employee'[Date]. The from and to tables are clearly identified in their own columns. The relationship is active, meaning by default this is how the tables are used together in visuals.
The Cardinality explains the ends shown on the relationship line: ONE row in 'Date'[Date] matches to MANY rows in Employee'[Date].
Cross filter behavior explains the arrow on the relationship line: One direction only: 'Date'[Date] row filter/slicer selections (direct and indirect) will also filter matching rows in 'Employee'[Date], but not vice versa. In this case, if I pick 2022 in the Date table through a slicer, the visuals showing data from Employee table will also only show Employees hired in 2022.
Security filter behavior explains how RLS will work on the tables: One direction: 'Date'[Date] row level security (RLS) filters (direct and indirect) will also filter matching rows in 'Employee'[Date], but not vice versa. In this case, if there is a RLS limiting what you can see to say 2022 in the Date table, you will only see the rows for 2022 in the Employee table too.
The cross filter behavior and security filter behavior are the same in this case, but they can different.
Finally, DirectQuery SQL join type is the same as toggling the Assume referential integrity toggle in the relationship properties pane or editor dialog. For import models there is no choice, it's always: OUTER join: non-matching rows included in a (Blank) group. So, if there are Employees hired in 2023 but there is no 2023 in the Date table yet, the slicer for year will show a (Blank) group to account for the non-matching rows. For DirectQuery where the DAX queries of visuals are converted to SQL queries, this is an OUTER join. To improve performance, especially when the underlying SQL tables have column store indexes on the columns used in the relationship, this will change it to an INNER join when assume referential integrity is checked. Just ensure that all rows are accounted for in each table, that is, if you have rows for employees hired in 2023, make sure there are matching rows in the Date table for 2023. This is known as referential integrity. Direct Lake models are not converted to SQL but also have this option.
Here is an example using DirectQuery semantic model:
What is neat is if you don't like my explanations, you can quickly adjust this DAX query to explain relationships in a way that makes sense to you!
I hope this is helpful when exploring the metadata and documenting your model with DAX INFO functions!