An example on creating a virtual table relationship with DAX TREATAS function.
What
Sometimes due to business logic we want to link two tables with a different key than the one already in the data model. For example, by selecting a specific year from the calendar table, we select the same year in another data table of the data model.
Code
The basic pattern is DAX CALCULATE with TREATAS + a possible CROSSFILTER to remove an existing table relationship in the calculation evalution.
With TREATAS first is mentioned the column which is being filtered and second the column which is automatically filtered like the first mentioned column.
Measure Customer Count first purchase month same as selected sales reporting month =
CALCULATE(
COUNTROWS(Customers);
TREATAS(VALUES(Calendar[Date]);Customers[DateFirstPurchase]);
CROSSFILTER('Calendar'[Date];Sales[OrderDate];NONE)
)
--This measure works also without crossfilter removal
--as the first purchase date matches to a sales date
--via the standard linking customerkey.
Theory
SQLBI has a good article about virtual relationships: https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/. The main point is that one should always prefer physical relationships when possible, and use virtual relationships only in single cases with small amount of data. SQLBI says that by their tests a virtual relationship is 70 times slower than a physical relationship. Personally, I have used virtual relationships now and then in single measures especially with dates, and those standard date tables are linked to fact tables with up to millions of rows of data. These single measures typically load in 1-3 seconds.
In the code COUNTROWS is used instead of COUNT. This should have performance advantage in most use cases: https://docs.microsoft.com/en-us/power-bi/guidance/dax-countrows. However, when there are large dimension tables DISTINCTCOUNT can perform better, see my post: The Cost of Relationships, Snowflake vs Star Schema.
Sample Power BI file
Example measures are created under the customers table in the example file.
https://drive.google.com/file/d/1rY8Azr5jljRNHTbFfqS4o7yjvWTFlN_q/view?usp=sharing
Sample data source: https://www.learndax.com/power-bi-sample-data-for-beginners-to-download/