Forum Discussion

Luci7's avatar
Luci7
Icon for Helper I rankHelper I
3 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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():


Luci7's avatar
Luci7
Icon for Helper I rankHelper 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
        Icon for Helper I rankHelper 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.