Direct Query - Dax Functions

[H_insight]

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

[v-jayw-msft]

Hi @H_insight ,

Can you share some sample data?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Best Regards,

Jay

[H_insight]

Hi @v-jayw-msft ,

It's a challenge to share a sample file when my question is about DIrectQuery? The connection is direct to the SQL server and not an import model. Hence why I shared a screen of the current model.

For now, I have built a custom SQL code to get me the join between the two tables. Shame it can't be done via DAX "Yet"! Thanks for the follow up 👍

Best Regards,

Hesham

[amitchandak]

@H_insight ,

Try a measure like

countx(filter(values(Table1[ID] ), Table1[ID] = max(Table2[ID])),Table1[ID])

[H_insight]

@amitchandak 

The result will always show 1