Dax measures or Power query Group By

[DanielBaker]

Following the maxim of transforming data as far upstream as possible,I do most work for Power BI in SQL server and Power Query, and as little as possible by creating DAX meaures. I don't really have any problems with performance, but I do have problems with my data model being complicated. Basically I'm creating separate tables in Power query that aggregate the data in different ways, rather than using DAX to return results - but this means I'm making a lot of tables, and I want to reduce these.

Do you have any advice? Am I maybe overly cautious about using DAX?

[Greg_Deckler · Accepted solution]

@DanielBaker I think you may be misinterpreting that maxim a bit. Aggregations are not transformations. Think of transformations as table joins, creating additional columns, etc. Having measures that do a SUM or an AVERAGE is not transformation, it's the intended purpose of visuals and simple DAX measures. If you want to SUM some Value column and have it grouped by Product for example, just put your Product in a visual and add a measure that does a SUM of the Value column. There is no need and I would say it is not best practice to create an aggregation table for the sole purpose of doing something like that. The exception (there always is one) is if you have a dataset with billions of rows and need an aggregation table to speed things up which is particularly useful in DirectQuery scenarios so that you are not hitting the source data system and thus subject to the latency of retrieving the data from a query to the source system.

[DanielBaker]

That is helpful - thanks

[HotChilli]

Is this some sort of phobia? I know some people have CalculatedColumn phobia but I've never heard of Measurephobia.  Daxophobia - would that be an irrational fear or a rational fear?  I think @Greg_Deckler has a phobia of Calculate.  I think i fear Quick Measures a bit.

Imagine waking up on Christmas Day and getting the Dax book from Alberto and Marco in your stocking....