Forum Discussion
Continued ContributorRELATED bug on calculated tables from live connection model
We've build a model that uses a live connection to a few other models. We then used SELECTCOLUMS to create two calculated tables derived from those models, to review data and build reporting off. And finally, we connected two of those tables together.
One of the calculated tables uses a calculated column with RELATED to bring in columns from a one of the other calculated tables.
This all worked fine when built, but today the same calculated table is returning an error:
Meanwhile, here is how two two calculated tables actually appear in the model:
They are linked and the link works fine if we build a table visual.
Any ideas why DAX is playing up when it worked originally?
Hi SevsBo
You're encountering this issue because the RELATED() function only works when there's a single-direction, many-to-one relationship from the current table to the lookup table. In your model, it appears that the relationship between Att_Inactive_Subaccounts and AttritionTable is many-to-many, which breaks RELATED().To work around this, you can:
Use an iterative + aggregation pattern with RELATEDTABLE() and something like MINX, MAXX, or SELECTCOLUMNS, for example:
IF(
ISBLANK(
MINX(
RELATEDTABLE(AttritionTable),
AttritionTable[Subaccount ID]
)
),
"Inactive",
"Active"
)Or: If possible, restructure the relationships in the source model (where the data is authored) to enforce a proper many-to-one direction. This may require help from the dataset owner or data modeler.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
3 Replies
johnt75Super User
RELATED will only work across a regular relationship, from the many side to the one side, and you have a many-to-many relationship. You could use LOOKUPVALUE or SELECTCOLUMNS with FILTER to get the value.
- Ritaf1983
Hi SevsBo
You're encountering this issue because the RELATED() function only works when there's a single-direction, many-to-one relationship from the current table to the lookup table. In your model, it appears that the relationship between Att_Inactive_Subaccounts and AttritionTable is many-to-many, which breaks RELATED().To work around this, you can:
Use an iterative + aggregation pattern with RELATEDTABLE() and something like MINX, MAXX, or SELECTCOLUMNS, for example:
IF(
ISBLANK(
MINX(
RELATEDTABLE(AttritionTable),
AttritionTable[Subaccount ID]
)
),
"Inactive",
"Active"
)Or: If possible, restructure the relationships in the source model (where the data is authored) to enforce a proper many-to-one direction. This may require help from the dataset owner or data modeler.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
