Check if value exists across DirectQuery - Import

[crobson29]

I have a composite model with a DirectQuery connection to a larger finance dataset and an import connection to a smaller table that has additional PO information. I was having problems with lines in the finance data being filtered out if there was not a PO associated with them, but I built a column that converts the blanks to null for the key column and added a null line in the smaller table so that is fixed.  I am still having problems with some POs that are not in the smaller table, but I still need to show up in the table visual.  I need to build a column or a measure that checks if a PO from the large dataset is in the smaller table.  I am currently adding every possible number that could be a PO, which is resulting in a billion row table, and that is not ideal.

[Anonymous · Accepted solution]

Hi,@lbendlin 

Thanks for your concern about this issue.

TREATAS can be used in other situations, but DirectQuery mode really doesn't support TREATAS.

And I would like to share some additional solutions below.

Hi,@crobson29 

I am glad to help you.  

According to your description, you want to check if value exists across DirectQuery – Import? 

If I understand you correctly, then you can refer to my solution. 

Since TREATAS is not supported in DirectQuery mode, consider using a LEFT OUTER JOIN in the query that loads the data into the model. 

Here's a little example that I hope you find helpful. 

In the Power Query Editor click Merge Queries: 

Select the corresponding table and corresponding field PO, and then select Left Outer in the Join kind. 

Click on the red circle in the picture to expand the table: 

Mismatched PO values are displayed as null values: 

Here's some official documentation, hope it helps:

Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

This approach will result in RI violations, and in generally bad query performance. Recommend you use TREATAS instead.

[crobson29]

TREATAS is not supported in DirectQuery mode unfortunately.