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 are 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.