DirectQuery issues with Related or Lookupvalue

[SevsBo]

I have a table, Table1, that is connected via DirectQuery and has a working bidirectional relationship with another table, Table2. I need to use some time intelligence with data from both to calculate difference in dates.

I tried an approach with Datediff, using a column from Table1 and Related(Column2) from Table2, but PBi said that column does not exist or the relationship does not allow for it.

I also tried adding that data directly into the DirectQuery Table using Lookupvalue, but then I get an error saying Column cannot be pushed to remote data source.

I do not have direct access to the DirectQuery table. Is there a way I can add data from one to the other or to use a different method for calculating date differences?

Orange is DirectQuery.

[v-kpoloju-msft · Accepted solution]

Hi @SevsBo,
Thank you for reaching out to the Microsoft Fabric Community Forum. Thank you @johnt75, for your inputs on this issue.

These are known limitations when working in Direct Query mode, especially with cross-table references in calculated columns.

Important Note: Power BI does not support calculated columns that reference values from another table when using Direct Query mode. This is because the expression cannot be fully translated (or "folded") into a single SQL query, and Power BI does not materialize the data locally in this mode.

For this reason, functions like RELATED, LOOKUPVALUE, or cross-table logic in calculated columns often fail or throw errors in Direct Query unless both tables come from the same source and the relationships support query folding.

If this helps, please consider giving a Kudos or marking it as Accepted Solution to assist others in the community.

Thank you for using the Microsoft Community Forum!

[v-kpoloju-msft]

Hi @SevsBo,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnt75]

You could use something like

Date diff =
AVERAGEX (
    'Table1',
    DATEDIFF (
        'Table1'[Date to use],
        LOOKUPVALUE ( 'Table2'[Date to use], 'Table2'[Key], 'Table1'[Key] ),
        DAY
    )
)

[SevsBo]

My whole problem is that DirectQuery doesn't allow for that to be done.

And I cannot append data to Table2 that I put in myself as there are more entries per row for same data for Table1 than Table2 so I get an error about multiple entries.

[johnt75]

Its meant to be a measure, not a calculated column.