Forum Discussion

Luci7's avatar
Luci7
Helper I
2 years ago
Solved

Cannot create a Time Intelligence Measure in the Warehouse

  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...
data warehouse
power bi
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Luci7's avatar
Luci7
Helper I
2 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • Luci7's avatar
      Luci7
      Helper I
      2 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Luci7 ,

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