topic Re: Cannot create a Time Intelligence Measure in the Warehouse in Data Warehouse

Cannot create a Time Intelligence Measure in the Warehouse

Luci7 — Tue, 12 Sep 2023 12:16:25 GMT

Hey everyone,

Good day! I've run into a weird scenario. In Power BI Desktop, I was able to create quick measures, especially the time intelligence ones. But the moment I throw the DAX into the Warehouse, it didn't work. The ".[Date]" part of the code is not working. I've got this column that's tagged as 'date' in my dataset, but it's like the Warehouse is playing blind. Anyone else tried making time intelligence measures in the Warehouse? A little guidance would be gold right now. 🤔

Below is an example of the DAY I copied and paste from Power BI Desktop to MS Fabric Warehouse:

Count of event_type MoM% =

IF(

ISFILTERED('table'[column]),

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

VAR __PREV_MONTH =

CALCULATE(

COUNTA('table'[column2]),

DATEADD('table'[column].[Date], -1, MONTH)

)

RETURN

DIVIDE(COUNTA('table'[event_type]) - __PREV_MONTH, __PREV_MONTH)

)

Kind regards,

Luci

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Fri, 15 Sep 2023 09:52:24 GMT

Hi Luci7 - Welcome to Fabric Community platform and thanks for posting your question here.

As I understand you are trying to execute a query with DATEADD() function in DAX . It seems like there is some syntactical error in the query. Please check the below points regarding DATEADD function in DAX:

1. The first column of the DATEADD function should have the type date and return a single-column data table.

2. When you add or subtract any interval from the date, you will get only those dates available in your date table.

3. After shifting the date interval forward or backward, the DATEADD function will only contain those shifted dates that are available in the data table; otherwise, it will return blanks

Sample Query: DATEADD(DimDate[Date],-1,MONTH)

For more details , kindly check this document: https://www.learndax.com/dateadd-dax-function-and-related-issues/

Since your query is more inclined towards PowerBI directly, I would recommend you to post your issue directly on PowerBI official community forum: Microsoft Power BI Community - Microsoft Fabric Community

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Wed, 20 Sep 2023 10:49:54 GMT

Hi Luci7 ,
Following up to see if the below comment was helpful. And, if you have any further query do let us know.

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Wed, 20 Sep 2023 10:49:38 GMT

Hi Luci7 ,
Following up to see if the comment was helpful. And, if you have any further query do let us know.

Re: Cannot create a Time Intelligence Measure in the Warehouse

Luci7 — Wed, 20 Sep 2023 14:18:26 GMT

Hi Anonymous ,

Thank you for the welcome and for your reply!

I thought would be useful to put my question here because the error I am only getting an error using this formula in Fabric, not Power BI Desktop. In Power BI Desktop I am using this formula successfully and it can be found in the "quick measure" part.

There, in Power BI Desktop I don't get any error, but in Fabric, using the same formula, I got an error and Fabric doesn't recognize my date column as a date type, because it gives me an error when I try to use formulas related to date, like for example, DATEADD.

Could you please inform if you use, in Fabric, any function related to time in a date colum type? It worked?

Thank you for your help!

Kind regards

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Thu, 21 Sep 2023 12:37:02 GMT

Hi Luci7 ,

I am able to use DAX DATEADD() in my Fabric Warehouse without any error.
Can you please cross verify your syntax and also try to share few screenshots in order to identify exact issue.

Working with DAX DATEADD():

Re: Cannot create a Time Intelligence Measure in the Warehouse

Luci7 — Tue, 03 Oct 2023 11:33:10 GMT

Hi Anonymous

I doubled checked and you're right, if we use:

DATEADD('table'[column], -1, MONTH)

It works in Fabric, but if you go to Power BI Deskto the correct syntax is

DATEADD('table'[column].[Date], -1, MONTH)

Do you know if Microsoft is changing the Time Intelligence DAX syntax in Fabric?

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Wed, 04 Oct 2023 12:02:57 GMT

Hi Luci7 ,

Thanks for using Fabric Community and reporting this .

I have reached for the internal team for help on this. I will update you once I hear from them.

Appreciate your patience.

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Tue, 10 Oct 2023 19:06:46 GMT

Hi Luci7 ,

We have an update from internal team.

If the two datasets have a different structure, its okay for the syntax to be different.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time#work-with-auto-datetime

In Power BI Desktop, a valid measure expression could read:

DAXCopy

Date Count = COUNT(Sales[OrderDate].[Date])

Note:

While this measure expression is valid in Power BI Desktop, it's not correct DAX syntax. Internally, Power BI Desktop transposes your expression to reference the true (hidden) auto date/time table column.

Hope this is helpful.

Re: Cannot create a Time Intelligence Measure in the Warehouse

Luci7 — Wed, 11 Oct 2023 07:56:28 GMT

Hi Anonymous ,

I see!! This is why! In Power BI Desktop we have the "auto date/time table" but not in Fabric, hence, the date column is not automatically converted to show date hierarchy.

Thank you for the clarification!! That helps a lot.

Have a nice day!

Kind regards.

Re: Cannot create a Time Intelligence Measure in the Warehouse

Anonymous — Wed, 11 Oct 2023 07:58:42 GMT

Hi Luci7 ,

Glad to know your issue got resolved.
Please continue using Fabric Community for help regarding your issues.