Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to 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 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.
That is helpful - thanks
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....
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
78 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
58 |