Forum Discussion
Newbie Question - Date Table in Power Bi, DAX or M ?
Hello everyone
I'm starting in Power BI and i have this "simple" question
If i need to create a Data Table in Power BI, whats is the best aproach?
Use Dax ou User PowerQuery/M
I've created 2 PBIX files, one for DAX and another to M, they are almost equal using Vertipaq in DAX Studio.
Can anyone diferences in performance or storage? and best aproach in real world projects.
Thanks in advance.
Fausto Caveiro
I recommend M/Power Query. It is recognized as imported data by DAX and has good compression. It can be fully dynamic as well, adding years as your data increases over time, and removing earlier years if you don't want to keep history forever. For example, last 5 years or whatever. See my blog on how to do this.
Creating a Dynamic Date Table in Power Query
If you use DAX, I recommened a calculated table, not a table with calculated columns. The latter will not perform as well.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
2 Replies
- edhans
Community Champion
I recommend M/Power Query. It is recognized as imported data by DAX and has good compression. It can be fully dynamic as well, adding years as your data increases over time, and removing earlier years if you don't want to keep history forever. For example, last 5 years or whatever. See my blog on how to do this.
Creating a Dynamic Date Table in Power Query
If you use DAX, I recommened a calculated table, not a table with calculated columns. The latter will not perform as well.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tablesHi Edhans
Many many thanks for you fast answer!
