Direct Query - Dax Functions

DAX DAX Calculations
1

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.

image
image751×464 15.2 KB

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.

Thanks

2

Hi @Hesham, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Include the masked demo pbix fileand any other supporting links and details.*

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hi @Hesham, we’ve noticed that no response has been received from you since the 30th of April. We are waiting for the masked demo pbix file, and other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hi @EnterpriseDNA, I appreciate that I didn’t upload any pbix file only as my question is for DirectQuery. If I upload a sample file, the solution will be based on Import Mode, which is not the case for me.

Please let me know your thought.

Best Regards,
Hesham

5

HI @Hesham,

As you mentioned your DAX formula is not working on Direct Query so my suggestion would be simply use Power Query to compare the two table and get the output as matched or mismatched.

Thanks
Mukesh

6

Hi @MK3010,

Thanks for sharing your suggestion. I have tried using PQ to compare the 2 tables by using the Merge option, but that will force me to import the data into PBI. Which again, take me to square one.

@EnterpriseDNA I will do some research on the available DAX functions within Direct Query and take it from there. Should that fail, I will build a custom SQL script to do the transformation on the server and get the cleaned data into PBI.

On that note, I will mark my comment as a Solution as it’s the only way to close this question.

Thanks all for the input :+1:

Regards
Hesham

1 Like