Query design per dataset mode
As mentioned in Chapter 1, Planning BI Projects, to the greatest extent possible data transformation processes should be implemented within data sources such as Azure SQL and Azure Synapse Analytics rather than via Power BI’s data transformation capabilities.
The presence of significant data transformation logic, such as joins, filters, and new columns, outside of an organization’s primary data warehouse or “source of truth” makes these solutions more difficult to understand and support. In addition, source systems are generally provisioned with more compute resources to handle data transformations and often include secondary data structures, like indexes, that speed up certain operations such as filters and joins.
If resource or time constraints make it necessary to apply data transformations in Power BI rather than source systems, Power Query (M) should generally be favored over DAX calculated columns and tables...
