Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Praveen_t
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.

 

2 ACCEPTED SOLUTIONS

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.

View solution in original post

mickey64
Super User
Super User

How about these steps?

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

 

Step 0: I use a simple DATA table below.

mickey64_0-1720749458870.png

 

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.

mickey64_1-1720749639752.png

 

 

View solution in original post

8 REPLIES 8
mickey64
Super User
Super User

How about these steps?

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

 

Step 0: I use a simple DATA table below.

mickey64_0-1720749458870.png

 

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.

mickey64_1-1720749639752.png

 

 

v-linhuizh-msft
Community Support
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:

 

11.png

2.Create a new table:

 

22.png

 

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:

 

33.png

 

5.The final result is shown in the figure:

1.png

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!

 

 

 

 

cath1ynn
Resolver II
Resolver II

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.

 

cath1ynn_0-1720664365210.png

 

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.

cath1ynn_1-1720667927988.png

 

cath1ynn_2-1720667943193.png

 

cath1ynn_3-1720667952868.png

 

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

 

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

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.

Please can you share the workbook

mickey64
Super User
Super User

I hope the information in the links below will help answer your questions.

 

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

ryan_mayu
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.