Power BI Dynamic filtering

[shashanm]

I need to filter my Order table based on the Conditions table. The catch is that my conditions table can have any number of rows.

I have my tables in the below format - 

Condition Set	BillTo_Code	Shipper_Code	Receiver_Code
C1	B1, B2
C2		S1, S3	R3
C3	B3, B4	S3, S4	R2, R3, R4

Order No	BillTo	Shipper	Receiver
1	B4	S4	R4
2	B2	S3	R2
3	B2	S3	R1
4	B4	S1	R3
5	B3	S4	R1
6	B5	S3	R3
7	B1	S3	R5
8	B2	S3	R6
9	B4	S5	R6
10	B3	S3	R2

The consitions need to be applied in the following way - 

1. For each row in the conditions table - it's an AND condition between the 3 codes. 

2. Then we UNION the results of each row

For the above table I have, my results would be -

1. For C1 - 2, 3, 7, 8 

2. For C2 - 4, 6

3. For C3 - 10

Final results - 2, 3, 4, 6, 7, 8, 10

The equivalent SQL in this case would be - 

SELECT OrderNo FROM Orders WHERE

(BillTo IN (B1, B2))   OR        --C1

(Shipper IN (S1, S3) AND Receiver IN (R3)) OR     --C2

(BillTo IN (B3, B4) AND Shipper IN (S3, S4) AND Receiver IN (R2, R3, R4))   --C3

I need to be able to loop through the conditions table as the number of condiiton sets can change dynamically.

Can someone please help with the implementation in Power BI?

[onurbmiguel_ · Accepted solution]

Hi @shashanm 

Please analise the following file. 

Conditions in orders.pbix

Look with details at the new "Condition Table" , i have made some changes !! 

Best regards

Bruno Costa | Power Participant

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

You can also check out BI4ALL's website and our data solutions!