Dax slow than M? Desktop refresh faster than service from dataflow source

[abv46mac]

Hi all,

I have a medium data model around 2gb with 4-5 mill of rows, because I have to merge queries in M and I thought that this type of merge made my model slower, it takes in desktop around 7 hours to update and 3 or so hours on service.

*all my sources are from dataflow

i made some changes:

-create two different queries, one with the data of this year and other with the historical

-when I refresh this table then I disable the update in editor

-the I use UNION in dax to append the tables, and use lookup value for the combine the values and get some data I need( this data before this change were merge in M)

with this change the data mode, refresh in 20 minutes, before around 7 hours, but in service it takes 3 or more hours.... but why??????

also my report are a quite slow right now.., so ¿is dax slower than M? Does the service slower with dax than M?

thanks!!! 

[Icey]

Hi @abv46mac ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[abv46mac]

Thanks, no the problem persist, I think Dax Union is less efficient dan Merge in M.. thats all. 

[Icey]

Hi @abv46mac ,

DAX and M address different situations.

M has its own “sweet spot,” which is data transformation and cleaning. DAX is completely unsuited for that sort of thing.

DAX uses functions to work on data that is stored in tables. Some DAX functions are identical to Excel worksheet functions, but DAX has many more functions to summarize, slice and dice complex data scenarios.

In essence: First you use Power Query (M) to query data sources, clean and load data. Then you use DAX to analyze the data in Power Pivot. Finally, you build pivot tables (Excel) or data visualisations with Power BI.

For details, you can refer to this document: M or DAX? That is the Question!

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.