A refresher pls - which is more efficient (Power Query or DAX) for data source calls? I'll explain.

[D_PBI]

Hi all,
Of late, and becoming more frequent, our numerous overnight data refreshes are failing via the PBI Service. The received error messages are indicating timeouts on our Azure SQL database. On a side note, I am looking into the Service Tier we use and then to possibly better stagger the refresh times.

The faced issue has me questioning the best route for report creation - Power Query or DAX. I'll explain.
For simplicity, let's say I have just two tables - CUSOTMER and SALES.
I've been asked to produce a report that has three report pages. Each report page is to show the combining the two tables with different filtering/grouping/self-joining logic, per page.

I see I have two options to do this:
Option 1:
Bring in the CUSTOMER and SALES tables individually and let them sit as separate queries in Power Query.
I reference the CUSTOMER query, naming the query _REF_CUSTOMER_ONE, and then join the SALES query to it. I then apply the filtering/grouping/self-joining logic as the report one needs.
I also reference the CUSTOMER query two further times, also joining the SALES query each time, and apply differing logic to the satisfy the two further report pages.
The resulting three queries will be names _REF_CUSTOMER_ONE,  _REF_CUSTOMER_TWO,  _REF_CUSTOMER_THREE. Remember each of these queries, due to having different filtering, contain very different result-sets.
I load each query to the PBI model and drag the query fields into the various visuals and let the visual's aggregation do its job.

Option 2:
Bring in the CUSTOMER and SALES tables individually and let them sit as separate queries in Power Query.

I load the raw CUSTOMER and SALES queries to the PBI model.
I then write multiple DAX expressions to replicate the filtering/grouping/self-joining (the same logic that was applied using PQ in Option 1).
The resulting DAX expressions/measures are then drag into the various visuals and let the visual's aggregation do its job.

I am wondering which option (Option 1 - duplication of source tables in PQ,  or Option 2 - non-duplication of source tables but duplication in DAX) would be most optimal for on the source Azure SQL database?  

In understanding this it may help me to eliminate (or help reduce) the data refresh timeouts that are happening against our source Azure SQL database.
Thanks.

[v-easonf-msft · Accepted solution]

Hi， @D_PBI 

It depends on your actual situation.

Please refer to Marco Russo's insight on Compare dax calculated column with power query calculated column.

When to use Power Query computed columns
You should use Power Query computed columns whenever the expression only depends on other columns of the same row of the table where you create the new column. This is the case for the Price Range column used in the example of this article.

If the data source is a relational database and you merge tables that can be joined in the data source efficiently, then it is a good idea to denormalize a table using Power Query functions – rather than importing multiple tables into the data model and then creating DAX calculated columns retrieving corresponding data from other tables using RELATED.

You should be careful when deciding whether to use Power Query computed columns, if you need to aggregate rows from other tables. The Power Query computed columns executes the aggregation on the data source, and this might take a long execution time. If this happens, you should consider a DAX calculated column to avoid a long processing times.

When to use DAX calculated columns
You should use DAX calculated columns whenever you cannot implement the same calculation in a Power Query computed column. The typical case is when aggregating data from other tables in the model. Another case would be the denormalization of tables coming from different data sources, because this would not be optimized using the query folding technique in Power Query.

---

Best Regards,
Community Support Team _ Eason