No of Days between 2 columns - Direct query mode

[Rana__Fahad]

Dears, 

I am trying to create a new column based on 2 tables on my database (Number of days between 2 dates), with Direct query mode. I used the following Dax: 

colname = DATEDIFF(

FIRSTDATE(table1[col]),

LASTDATE(table2[col]),

DAY). but I got the following error : Function 'FIRSTDATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Thank you

[DOLEARY85]

Hi,

you could try Min and Max instead of first/last

Measure =

CALCULATE(

    DATEDIFF(

        MIN(Table2[col]),

        MAX(Table1[col]),

        DAY

    ),

    ALL(Table1),

    ALL(Table2)

)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Rana__Fahad]

Dear DOLEARY85Thank you for you time & effort , I want to create a column not a measure. I have many records that I need the number of days for each record. 

[DOLEARY85]

It should still work as a column, are you getting an issue when you try it?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Rana__Fahad]

I appricate your fast response , yes I am getting the same result for all records. 

[DOLEARY85]

Ah okay, 

let's try removing the all statement and calculating min/max individually,

Column =

    DATEDIFF(

        CALCULATE(MIN(Table2[Column2])),

        CALCULATE(MAX(Table1[Dat1])),

        DAY

    )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85]

Okay, i think you're going to have the same issue with most functions that are going to require row context.

A measure with the same calculation should work and provide a number for each row as you're using calculate to change the context. 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Rana__Fahad]

I got the following error : Function 'MIN' is not allowed as part of calculated column DAX expressions on DirectQuery models.

[Anonymous · Accepted solution]

Hi @Rana__Fahad ,

When using DirectQuery, certain DAX functions are not supported because they cannot be converted into SQL syntax.
Try use native query in desktop or in datasource,like:

SELECT 
    table1.col1,
    table2.col2,
    DATEDIFF(day, table1.col1, table2.col2) AS colname
FROM 
    table1 
INNER JOIN 
    table2 
ON 
    table1.id = table2.id

Import data from a database using native database query - Power Query | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum