cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mm_engstrom
New Member

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

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

View solution in original post

23 REPLIES 23
buckeyepilot67
New Member

buckeyepilot67_0-1605622779160.png

Been having issues trying to calculate a rolling 14 day average.  Getting the same error trying to create a measure multiple ways.

bluebird1996
Regular Visitor

I had the same error others had, and the following auto generated code worked fine for me, after I *cleared my filters* of time-based criteria.  
 
 
_INDY rolling average 2 =
IF(
    ISFILTERED('Daily Kit Count - RCV'[_ WeekEnding]),
    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 = LASTDATE('Daily Kit Count - RCV'[_ WeekEnding].[Date])
    RETURN
        SUMX(
            DATESBETWEEN(
                'Daily Kit Count - RCV'[_ WeekEnding].[Date],
                DATEADD(__LAST_DATE, -1, DAY),
                __LAST_DATE
            ),
            CALCULATE(SUM('Daily Kit Count - RCV'[_INDY]))
        )
)
Anonymous
Not applicable

For anyone still encountering this issue, this fix worked for me. In retrospect this is probably quite obvious to the experienced users here, but for those of us still learning it isn't quite so clear.

 

Assuming you have created a custom date dimension, right click on the table name in your fields list. Navigate down to "Mark as date table" and make sure it is checked. Now I no longer recieve the error at the top of the thread.

I did this but message " The Date Column Can't have gaps in dates" and I am sure there are no gaps

aviral
Advocate III
Advocate III

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

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

View solution in original post

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.")"

error.PNGerror 2.PNG

I have this error and I am sure that column is exist identically by name

Hi 

 

i get following error, please advise

 

 

Capture.PNG

 

 

Jayanthan

 

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]) )
dh731r
Frequent Visitor

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

Hi @dh731r

 

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

"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???
Anonymous
Not applicable

I have the same issue. Any news?

Hi @dean_mernagh

 

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

Hi @aviral, Appreciate your help, i can't post a screenshot as it won't let me but i did format as a date and also ensured that the locale was also on the correct setting. Please see below for code. 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]) )

Sorry for a late response. 

 

Make sure in the settings "Time Intelligence" is ticked as shown below. 

 

2019-01-24_10-49-32.png

Sorry i can't see the image- can you explain please?

 

Thank you.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors