cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Newbie Question - Date Table in Power Bi, DAX or M ?

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
Highlighted
Super User VI
Super User VI

Re: Newbie Question - Date Table in Power Bi, DAX or M ?

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Frequent Visitor

Re: Newbie Question - Date Table in Power Bi, DAX or M ?

Hi Edhans

 

Many many thanks for you fast answer!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors