cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Filter Sales Order based on Product Type

Hi All,

I have a Sales Order number with Multiple Product type. if any one of the Product types has Installed then I want to eliminate the whole order.

For example

Sales Order#          Product     Amount

123                            AFF          \$1000

123                          Install        \$1500

124                           AFF-200    \$1200

124                             FAS          \$3000

125                            Install        \$100

Based on above data I expected out put is

Sales Order#          Product     Amount

124                           AFF-200    \$1200

124                             FAS          \$3000

But My Sales order number is coming from Sales Order table, Product type is coming from Product table & Amount is coming from Sales Booking table all attributes are in different table. How to solve this problem Any help will be appreciated.

8 REPLIES 8
Super User

If speed is important to you, you should use 'Measure'.

Step 0: I use a simple DATA table below.

Step 1: I make two mesures below.

M_Product_All = CALCULATE(CONCATENATEX('DATA','DATA'[Product]),ALLEXCEPT(DATA,DATA[Sales Order#]))

M_Flag_Value = IF(CONTAINSSTRING('DATA'[M_Product_All],"Install")=TRUE(),1,0)

Step 2: I add a matrix and use the Filter pane below.

Community Support

Hi  @Praveen_t ,

Thanks for the reply from  @cath1ynn @mickey64 @ryan_mayu  . I tried to create a sample data myself based on the requirement. Here is my solution:

1.The tables I created are as follows:

2.Create a new table:

The code is as follows:

``Table= FILTER(ALLSELECTED(SalesBooking[SalesOrder#],'SalesBooking'[ProductID]),'SalesBooking'[ProductID] = 2) ``

3.Then create a measure. Here is the DAX code:

``````Measure =
VAR a = VALUES('Table'[SalesOrder#])
RETURN CONCATENATEX(FILTER('SalesBooking','SalesBooking'[SalesOrder#] IN a),1) ``````

4.Filter out cases where the measure is not 1:

5.The final result is shown in the figure:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helper I

Assuming your table models look like this where the 3 attributes you mentioned are all in different tables, if you have the relationship between the tables set up correctly, you should be able to just add a filter as required.

I would add a calculated column in the Sales Booking table that bring in the Product Code.

`ProductCode = RELATED('Product'[Product Code])`

Then in the Sales Order table, create a new calculated column called HasInstallProduct that checks if that particular order has any product where the Product Code is Install.

`HasInstallProduct =    VAR orderNo = 'Sales Order'[Order No]    RETURN IF(ISBLANK(LOOKUPVALUE('Sales Booking'[Order No], 'Sales Booking'[Order No], orderNo, 'Product'[Product Code], "Install")), "No", "Yes")`

Then you can use that new field as a filter or use it in a Slicer.

Here's the sample SalesOrderTemp Excel file and the sample SalesOrderSample PBIX file.

New Member

Cath1ynn,

Your solution worked but it causing huge performance issue because we have millions of records. Is there any simplest way to do it. Thanks for you support

Helper I

Fair enough. Why don't you try this approach instead.

Create a new calc column in the SalesBooking table that checks if the product is an Install product

`IsInstallProduct = IF('Sales Booking'[ProductCode] = "Install", 1, 0)`

Then in the SalesOrder table, modify the previous HasInstallProduct column to:

`HasInstallProduct =    VAR orderNo = 'Sales Order'[Order No]    RETURN IF(        CALCULATE(            SUM('Sales Booking'[IsInstallProduct]),            'Sales Order'[Order No] = orderNo        ) > 0, "Yes", "No")`

LOOKUPVALUE does row by row calculation, whereas CALCULATE perform table wide operation with aggregation so it should be a lot faster in terms of performance.
New Member

Please can you share the workbook

Super User

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Super User

could you pls provide the pbix file or  the sample data in three tables ?

Proud to be a Super User!