Forum Discussion
Time intelligence does not work
Got a solution from PBI support.
========SOLUTION TEXT STARTS========
One easy way to solve the ussie is to transform the date from a text to a date value using the UK format:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVRRcsUgCLxLvjsjYIzmLJl3/2tUGht3ie2fDALLsnBdW9YkOZnosX2+rq2/b7P+mFmSnNPbTbVp6p5k589o9sxtmnYO7x1rNamAaUkVzJbEZmx/S4FUQiDde4C3cCGODf32ovjZG0QYQrFaBhuj7jG8a3IOQiV7LHR7x+dzcPWQI7rAPFI1guFeZXJWsZMNgPEQO2BkBqmMqr3mC6i8BeBKAlfGQgqx/Fm5bpcZSkUbNRhEqCwzhwGpfAoVUhWObaRYBwnkOO2V2QCJ2q939FtfTJZF3SkzYxh10dEwhToKG/rmmTR5siYZZCQnaLKwzCphDgNdaANbMDKDroRX0jVZmEnWBtGe+W4odRRQ9bq47GH3+5vMzCDfV+XvI+MwYNnDJQw89/HhBMOdjKdAaXF8KCezAXXjuHk1/F7xqaeRFa57EDmO2f5lA2b0HNUe+/kG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")
in
#"Changed Type with Locale"
In the Query Editor > Advanced query editor, you have to replace your
#"Changed Type with Locale"= ……………………………….” (whatever is there in middle), please replace this with the below one and close and apply and now run the DAX query will work
#”Changed Type with Locale” = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")========SOLUTION TEXT ENDS========
Advocate IVI am facing the same issue.
Getting the following error:
Time intelligence quick measure can only be grouped or filtered by the power-bi provided date hierarchy or primary date column
When I try to convert the column to date format it again throws the following error:
We cannot automatically convert the column to date type. The column is being calculated from an existing date column and calculation looks like this:
RfS Date =
VAR filteredTab = SUMMARIZE(FILTER(myTable, <<expression>>,myTable[DDate],"abcDate",myTable[DDate])
RETURN if(COUNTROWS(filteredTab)>0,MINX(filteredTab,[abcDate]),BLANK())
=========
I am also facing issue in converting the data type of any column from one type to another. This issue was not there in older versions.
I also opened a ticket with them but doesn't seem to be working out.
I am not sure but this seems to be a bug.
Advocate IVGot a solution from PBI support.
========SOLUTION TEXT STARTS========
One easy way to solve the ussie is to transform the date from a text to a date value using the UK format:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVRRcsUgCLxLvjsjYIzmLJl3/2tUGht3ie2fDALLsnBdW9YkOZnosX2+rq2/b7P+mFmSnNPbTbVp6p5k589o9sxtmnYO7x1rNamAaUkVzJbEZmx/S4FUQiDde4C3cCGODf32ovjZG0QYQrFaBhuj7jG8a3IOQiV7LHR7x+dzcPWQI7rAPFI1guFeZXJWsZMNgPEQO2BkBqmMqr3mC6i8BeBKAlfGQgqx/Fm5bpcZSkUbNRhEqCwzhwGpfAoVUhWObaRYBwnkOO2V2QCJ2q939FtfTJZF3SkzYxh10dEwhToKG/rmmTR5siYZZCQnaLKwzCphDgNdaANbMDKDroRX0jVZmEnWBtGe+W4odRRQ9bq47GH3+5vMzCDfV+XvI+MwYNnDJQw89/HhBMOdjKdAaXF8KCezAXXjuHk1/F7xqaeRFa57EDmO2f5lA2b0HNUe+/kG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")
in
#"Changed Type with Locale"
In the Query Editor > Advanced query editor, you have to replace your
#"Changed Type with Locale"= ……………………………….” (whatever is there in middle), please replace this with the below one and close and apply and now run the DAX query will work
#”Changed Type with Locale” = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")
========SOLUTION TEXT ENDS========
jayanthanHelper III
Hi
i get following error, please advise
Jayanthan
Having an issue here as well. I've created a separate date mapping table, using PowerBI's date foremat and is linked with the data. However the error shows: Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column
- "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."It's all i've got back for a full day of trying and i can't for the life of me figure it out- i have tried all suggestions and nothing is working.Can ANYBODY help PLEASE???
- aviral
Hi jayanthan
Could you please post the M code from the query because it seems there is some error in the way locale setting is being applied.
- Hi jayanthan, Do you mean the below? Appreciate your help... Sales _Volume YTD = IF( ISFILTERED('tbl_Sales'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), TOTALYTD(SUM('tbl_Sales'[Sales _Volume]), 'tbl_Sales'[Date].[Date]) )
I have this error and I am sure that column is exist identically by name
After Doing this, it still give the message "
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.")"First you will have to make sure the Date column is not blank.
Then you can use the DAX from the example.
3 Months rolling average =IF(ISFILTERED('SALES'[Date]),ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),VAR __LAST_DATE = ENDOFMONTH('SALES'[Date].[Date])VAR __DATE_PERIOD =DATESBETWEEN('SALES'[Date].[Date],STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)),__LAST_DATE)RETURNAVERAGEX(CALCULATETABLE(SUMMARIZE(VALUES('SALES'),'SALES'[Date].[Year],'SALES'[Date].[QuarterNo],'SALES'[Date].[Quarter],'SALES'[Date].[MonthNo],'SALES'[Date].[Month]),__DATE_PERIOD),CALCULATE(SUM('SALES'[Invoiced Quantity]), ALL('SALES'[Date].[Day]))))