Forum Discussion
How can I perform an OR filter on a DirectQuery, based on a second table?
Hi Anonymous
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
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.
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.
Hi Anonymous
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.
No, I have read-only access to the data set. It is a large organization and the reporting side doesn't have write access.
Hi Anonymous
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
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.
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.