Calendar "manipulations" on Dax or M language

[Ritaf]

Hi all,

I need to create a model with calendar which will let me to make calculations based on working days only. I saw few methods to do this in Dax which are pretty simple and Some sophisticated code in M. What is the best practice in this case?

Thanks a lot.

Rita

[TomMartens · Accepted solution]

Hey @Ritaf ,

basically it's a good idea to create tables in Power Query / M as tables/columns that are created in DAX will suffer some disadvantages. These disadvantages are described in this article:

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

There is an important topic that is not mentioned in the above article and that is the "portability" to a dataflows, providing reusability throughout your company.

Personally I start with "DAX", due to the following

• DAX is still more simple to me than M
• DAX does not need a full refresh of the table

When I'm done (for the 1st iteration) I port my DAX to M.

Hopefully this provides some ideas.

Regards,

Tom

[TomMartens · Accepted solution]

Hey @Ritaf ,

basically it's a good idea to create tables in Power Query / M as tables/columns that are created in DAX will suffer some disadvantages. These disadvantages are described in this article:

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

There is an important topic that is not mentioned in the above article and that is the "portability" to a dataflows, providing reusability throughout your company.

Personally I start with "DAX", due to the following

• DAX is still more simple to me than M
• DAX does not need a full refresh of the table

When I'm done (for the 1st iteration) I port my DAX to M.

Hopefully this provides some ideas.

Regards,

Tom

[Ritaf]

Hi ,

Thank you for your explanations. 

Eventually ia did it on dax, because of lack of M knowledge, it just didn't work  in other way.

May be you have a recomendation of course / book/ guide of M language.

Today i am able to use only functions with "buttons" without writing code.

[TomMartens · Accepted solution]

Hey @Ritaf ,

I guess most of us are creating the Calendar table using DAX, and just a few of us have that much rows inside their Power BI SSAS tabular (on-premises / cloud basesd) data models that the points from first post have to be considered.

For this I would recommend that you should use DAX until you feel comfortable using DAX, and then start using M, to avoid unnecessary confusion.

From my point of view, there is no such thing as a gentle introduction into the world of M (beyond point and click).

My favorite books about Power Query / M are:

• https://www.amazon.com/Data-Monkey-Guide-Language-Excel/dp/1615470344/ref=sr_1_1?keywords=M+is+for+t...
• https://www.amazon.com/Collect-Combine-Transform-Business-Skills/dp/1509307958/ref=sr_1_1?keywords=g...

These blogs are a wealth of wisdom (but maybe not that easy):

• https://bengribaudo.com/
• https://www.thebiccountant.com/

This blog has a lot to offer, without a special order

• https://radacad.com/blog

I guess this will keep you from getting bored during the upcoming holidays 🙂

Regards,

Tom