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

Time intelligence does not work

Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

How does time intellingence work on a data warehouse with own data hierachy or what does primary dat column mean?

1 ACCEPTED SOLUTION

Accepted Solutions
aviral Regular Visitor
Regular Visitor

Re: 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========

17 REPLIES 17
ErikHorsthuis Frequent Visitor
Frequent Visitor

Re: Time intelligence does not work

I have the same problem. Using Quick measure with Time Intelligence generates Dax-function with this error-message. I have no problem in creating manually the Dax-function for for example TotalYTD. I have a date-table with correct date-cllumn as primary key. So why is the Quick measure not working? I use the latest Power BI Desktop version, I downloaded today.

aviral Regular Visitor
Regular Visitor

Re: Time intelligence does not work

I 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. 

Screen Shot 2017-10-17 at 5.05.04 PM copy.pngScreen Shot 2017-10-17 at 5.07.59 PM.png

aviral Regular Visitor
Regular Visitor

Re: 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========

jayanthan Member
Member

Re: Time intelligence does not work

Hi 

 

i get following error, please advise

 

 

Capture.PNG

 

 

Jayanthan

 

dh731r Frequent Visitor
Frequent Visitor

Re: Time intelligence does not work

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

dean_mernagh Frequent Visitor
Frequent Visitor

Re: Time intelligence does not work

"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 Regular Visitor
Regular Visitor

Re: Time intelligence does not work

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.

aviral Regular Visitor
Regular Visitor

Re: Time intelligence does not work

Hi @dh731r

 

Please post some piece of code and/or screenshot of relationship table to understand the issue. 

aviral Regular Visitor
Regular Visitor

Re: Time intelligence does not work

Hi @dean_mernagh

 

Please post some screenshots to confirm that the data type for the column in question is date/time.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 284 members 3,011 guests
Please welcome our newest community members: