Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
What's the best partice for adding a new (calculated) column to a source? Calculate the column in the SQL Query, or add the column later with DAX?
Let's say I would like to create a new column, which is basically just a flag. If the status of the order is "COMPLETED", then 1, else 0. Should I modify my query and add the column there, or use DAX for it?
I've you've got large datasets then it can provide better compression if it's a SQL column/expression over a DAX column.
If you can add as a DAX measure then it's not going store any data and calculated when needed.
However measure cannot be used for slicers.
There is a 3rd option add the Competed state in M (Query editor). Not sure if this is better or worse than in SQL.
My preference is SQL, M, DAX Measure then DAX calculated column.
I also find SQL is easier to manage change than lots of DAX columns.
Here is an post on the storage.
https://www.sqlbi.com/articles/storage-differences-between-calculated-columns-and-calculated-tables/
And another one which recomends avoiding calculated columns.
https://powerpivotpro.com/2014/10/5-common-mistakes-made-by-self-taught-dax-students/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |