DAX vs QUERY...which is faster?

[rolo]

Hello,

There are many situations where I can arrive to my goal doing 1 of these 2 things:

1. I can add some custom column in Power Query
2. I can buil some DAX in Power Pivot

For example:
I have a table with Sales in US dollars
I have another table with USD / EUR exchange rates
I need to convert USD sales to EUR sales
I can do it both with Power Query or DAX

DAX is always faster or slower than Power Query?
Is there a general rule for this or it depends on each kind of problem?

Thanks for your suggestions and comments!

 

[GraH - Guido]

Hi, Have a read of Marco Russo's insight on https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 

[Chihiro]

Is the data in single table? Or is it split between fact and dimension table?

If it's split into dimension table, generally speaking DAX measure (note not calculated column) would be more efficient.

In most cases, you should avoid calculated column(s) in DAX. Only instance where this may be warranted is when data is from another related table (i.e. dimension table) as doing the same operation in PQ can take longer.

PQ calculation slows down data load, where as DAX calculated column will slow down visual update on interaction with slicer etc. Most cases, if you do data load during after hours (or early in the morning) once. It's more desirable to have better performance during interaction with report.

Typically, I'd recommend using View/Stored Procedure where you can, to optimize performance.

 

[rolo]

Thank you for your answers!