Welcome to the next piece of our series of articles describing the challenges we handled while working on the “Snowflake Warehouse and AAS Tabular Model” project.

We are happy to share our recent findings that helped us implement specific requirements in the tabular model project on which we have been working for a while.

In a typical tabular model project, relationships between tables define how entities interact with each other which directly affects calculations in DAX measures, for example. However, when user requirements become more complex, we encounter scenarios where existing relationships between tables become undesirable.

1. Ignore a Relationship
The first use case involves a table of cash flows linked to both deals and funds tables. Our model already has another way to link deals and funds when not dealing with cash flows. We observed that in measures where we run calculations over data unrelated to cash flows, the model inadvertently excludes combinations of companies and funds that lack records in the cash flows table.

We resolved this issue by adjusting the DAX expressions to ignore the relationship with the cash flows table using the CROSSFILTER() function:

CALCULATE(
[Your DAX expression here],
CROSSFILTER(F_CASHFLOWS[D_DEAL_KEY], D_DEAL[D_DEAL_KEY], None)
)

This function can change the direction of an existing relationship in the model. Passing “None” as the third parameter means that no cross-filtering occurs along this relationship, effectively disabling it within the scope of that specific DAX expression.

2. Switch Between Relationships
Another use case involves a regular relationship between the cash flows and funds tables. However, our client required that, under certain conditions, some cash flows should be linked to an alternative fund rather than the original one.

To address this, we added an extra column to the cash flows table to store the ID of the alternative fund (D_FUND_KEY_SEC). Since the tabular model does not allow multiple active relationships between two tables, we defined the additional relationship but kept it inactive by default.

Then you can programmatically switch the relationship that should be used in the scope of the particular expression by using the USERELATIONSHIP() function:

CALCULATE(
[Your DAX expression here],
USERELATIONSHIP(F_CASHFLOWS[D_FUND_KEY_SEC], D_FUND[D_FUND_KEY])
)

This function activates the specified relationship within the scope of the expression while temporarily deactivating any other relationships between the same tables.

We hope you find these tips helpful in managing relationships within your tabular model projects. Stay tuned for more insightful posts on this topic!

00000