Forum Discussion
Advocate IDAX Year() Not Returning the Year
I have gotten some unexpected results when using DAX Year() - the year isn't returned in some cases. As a comparison, I created a new column the query editor, which always returned the correct value. Below are my formulas & results in a table:
DAX: Year = Year(SampleSheet[Date])
Query Editory (M): Date.Year([Date])
Is there a reason that YEAR() is not always returning the actual year?
Also, when it is possible to do the same thing in the Query Editor and DAX are there some guidelines around which to use? I've looked into this a bit and haven't been able to find a good answer.
Hi @Dave,
When you adding a calculated column using Year() DAX function, it builds an integer a column.
And by default, all integer cols are summarized by using sum, that's why you have the total under your year column.
Open you model, select your table and "year" column, go to "Modeling" menu and change the property "Default Summarization" from "Sum" to "Do not summarize"
6 Replies
fbrossardKudo Commander
Hi @Dave,
When you adding a calculated column using Year() DAX function, it builds an integer a column.
And by default, all integer cols are summarized by using sum, that's why you have the total under your year column.
Open you model, select your table and "year" column, go to "Modeling" menu and change the property "Default Summarization" from "Sum" to "Do not summarize"
- Greg_Deckler
Community Champion
Brilliant, didn't eve see that.
- fbrossard
Thx Greg_Deckler.
@Dave, sometimes you could have an integer column that of want to sum by default.
Nevertheless, you can modify the aggregation function at any time for any visual just by picking it :
- Dave
Thank you much! Setting the "Default Summarization" to "Do Not Summarize" resolved my issue. Also, thanks for all input on when to use M vs DAX.
- ImkeF
You can find some performance aspects of M vs DAX here:
https://blog.oraylis.de/2016/02/relative-week-column-with-dax-power-query/
"...Actually the Power Query version is slightly faster, because the Vertipaq-Engine can do a better compression with a column which is set during data load. The DAX generated columns will be created during processing the model. This has two negative impacts. First the compression won’t be as good and second every time the cube is processed, for example when a new measure is created, Power BI Desktop will recreate the two columns, which will take more time..."
- Greg_Deckler
Something wonky, not able to recreate. I was using "Enter Data" and just typed in the date time values as shown in your example below. All was good. What is the data source? In your model, if you select your Date column, on the Modeling ribbon, is the Data Type set to "Date/Time" and is the Format "*3/14/2001 1:30:15 PM (G)"?
As for M versus DAX I'd say that's a pretty obvious choice in your case! :)
Seriously though, there are different schools of thought there. One school of thought is to do as much as humanely possible in M to transform the data to how you want it before it gets to your model. However, one should also consider supportability and maintainability as well. For example, if I have to do some of the work in DAX, then I favor doing it all in DAX so that I am not mixing languages. Nobody wants to support a C#, java, perl, ruby mish mash of sadness. And, who will be maintaining it long term matters in terms of their skils in my opinion as well. Can they handle M or is DAX a better fit?
