How can I perform an OR filter on a DirectQuery, based on a second table?

[mmoore]

I have a very large data set of purchase records which I am accessing via DirectQuery. 

I need to find all records where either (a) Category = Transportation or (b) Supplier is in a list of targeted suppliers

The list of targeted suppliers is in an Excel file on Sharepoint, and accessed in Import mode.

How can I filter the purchase records table based on the suppliers listed in the Target Supplier table?

In real life the unfiltered Purchase Records have millions of rows, and the Target Supplier table has around 100. 

As I understand it, since I am using DirectQuery I cannot use PowerQuery. It seems that RELATED and Natural Joins are also out. 

I am pretty new to PowerBI but have a lot of other data experience (SQL, Python, R). 

I'm running PowerBI desktop Version: 2.112.1161.0 64-bit (Dezember 2022). 

[mmoore]

Thank you for the advice. Unfortunately I get the error: 

> The column 'Purchases'[_IF] cannot be pushed to the remote data source and cannot be used in this scenario.

I believe this is because the Purchases data source is a DirectQuery SAP data source, and the operations available are limited. 

[v-xinruzhu-msft]

Hi @mmoore 

Do you have the edit permission of the dataset?

You may need to confirm that your permission of the data source, it can effect your operation of the dataset.

Best Regards!

Yolo Zhu

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

[mmoore]

No, I have read-only access to the data set. It is a large organization and the reporting side doesn't have write access. 

[v-xinruzhu-msft · Accepted solution]

Hi @mmoore 

A calculated column on an import table can refer to other tables, but a calculated column on a DirectQuery table can still refer only to columns on the same table, but you can use a calculated table.

Maybe you can refer the follwing example.

Create a new table

Table 2 = SUMMARIZE(FILTER('Table',OR([Category]="Transportation",[Supplier] in VALUES('Table (2)'[Target Supplier]))),[Purchase ID],[Supplier],[Category],"IF",if([Category]="Transportation"&&[Supplier] in VALUES('Table (2)'[Target Supplier]),"Kept because target su and tra",IF([Category]="Transportation","Kept because  tra",IF([Supplier] in VALUES('Table (2)'[Target Supplier]),"Kept because target su "))))

You can refer to the following links about the limiations of direct query in composite mode

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#considerations-a...

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#calculated-table...

Best Regards!

Yolo Zhu

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

[mmoore]

Thanks, this did it! Because my data set is so large I had to hard code a date filter into the FILTER function for now, but now it runs and returns correct data.

[v-xinruzhu-msft]

Hi @mmoore 

You can refer to the following example.

You can create a new column

_IF = if([Category]="Transportation"&&[Supplier] in VALUES('Table (2)'[Target Supplier]),"Kept because target su and tra",IF([Category]="Transportation","Kept because  tra",IF([Supplier] in VALUES('Table (2)'[Target Supplier]),"Kept because target su ")))

Then filter the column to show data without blank value.

Output

Note: Based on your large dateset, it may take a long time to calculate it.

Best Regards!

Yolo Zhu

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