Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Datediff Problem With Direct Query

Hey guys! I have a problem with the DATEDIFF formula in Power Bi. I need to perform a DATEDIFF between two dates, which are in a fact table with more than 17 million rows. The difference number be...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Marcelo.

Exceptionally in this project, I do not have and cannot optimize the database.
I need to solve it within Power Bi.
I've tried creating custom columns too, but it gives the same error.

 

Thanks

Keven

marcelsmaglhaes's avatar
marcelsmaglhaes
Super User
2 years ago

You tried to create on Power Query or using DAX? If you used DAX, try doing this using Power Query. If the error persists, than you can calculate the date differences as part of your ETL process before importing the data into Power BI. This would involve writing SQL queries or using database functions to perform the calculation...

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Only in DAX. I know that it works in Power Query, however, in this project, we only have Direct Query and it is unfeasible to use Power Query, as well as, we cannot solve it in the bank.
    The fact is, it's basic arithmetic, which could be easily solved in ETL, Banco or Power Query. But the case specifies the resolution via DAX, as it is in Direct Query.
    And the difficulty lies exclusively in DATEDIFF transforming thousands of lines in days, communicating with the Bank in DQ.

    • marcelsmaglhaes's avatar
      marcelsmaglhaes
      Super User
      2 years ago

      And if you use a direct dax like dae1 - date2, without datediff function.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        I tried too, it didn't work. I also tried to transform it into an integer, but it doesn't work via DQ. :s