Forum Discussion
How can I perform an OR filter on a DirectQuery, based on a second table?
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).
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.
6 Replies
Hi Anonymous
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.
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.