Direct Query - Dax Functions

Hi All,

I am running a reconciliation exercise between two databases in Power BI. I have chosen direct query mode due to the large volume of data. Below is a simplified model for demonstration, where both databases (Table 1 & 2) have:

• Date & ID columns. ID Column does have blanks.
• The active relationship is between the Date column, and the inactive one is between ID’s.

My ask is for matching the tables by “ID” to show “Matched” & “Mismatched” via direct query. I think there is two options to do that:

• Custom SQL to perform that on the server (I would like to see one table with all records from table 1 & 2 & new matching/mismatch column)
• Using DAX

I would like to use the 2nd option, DAX. Tried already but not all functions would work with Directquery mode.

Here is my attempt but via Import Mode, which works:

Matching Records :=
VAR _matchinginT2 = LOOKUPVALUE(table1[id],table1[id],table2[id])
Return
IF(NOT(ISBLANK(_matchinginT2)),”Matched”,”Mismatched”)

Unfortunately, the LOOKUPVALUE does not work in direct query.

Any input is highly appreciated.

Thanks