You say:
Avoid relationships on calculated columns. This guidance is relevant to databases where you need to do multi-column joins. Power BI today doesn't allow a relationship to be based on multiple columns as the FK/PK. The common workaround is to concatenate the columns together using a calculated column, and base the join on that column. While this workaround is reasonable for imported data, for DirectQuery, it results in a join on an expression. That result commonly prevents use of any indexes, and leads to poor performance. The only workaround is to actually materialize the multiple columns into a single column in the underlying database.
As I understand, Multi-column joins are still possible if they are done using none loading queries. So we can setup a query doing multi-column joins at the schematic Power Query level. Please let me know if you don't agree, as I find the wording "The only workaround is to actually materialize the multiple columns into a single column in the underlying database" not suggesting this would be possible
Document Details
⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.
ID: 1ed1625f-12a2-5962-2959-696188711efe
Version Independent ID: d8796367-6bd1-c63c-db86-59eb5a66c213
You say: Avoid relationships on calculated columns. This guidance is relevant to databases where you need to do multi-column joins. Power BI today doesn't allow a relationship to be based on multiple columns as the FK/PK. The common workaround is to concatenate the columns together using a calculated column, and base the join on that column. While this workaround is reasonable for imported data, for DirectQuery, it results in a join on an expression. That result commonly prevents use of any indexes, and leads to poor performance. The only workaround is to actually materialize the multiple columns into a single column in the underlying database.
As I understand, Multi-column joins are still possible if they are done using none loading queries. So we can setup a query doing multi-column joins at the schematic Power Query level. Please let me know if you don't agree, as I find the wording "The only workaround is to actually materialize the multiple columns into a single column in the underlying database" not suggesting this would be possible
Document Details
⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.