Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to Solution.
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)
HasInstallProduct =
VAR orderNo = 'Sales Order'[Order No]
RETURN IF(
CALCULATE(
SUM('Sales Booking'[IsInstallProduct]),
'Sales Order'[Order No] = orderNo
) > 0, "Yes", "No")
How about these steps?
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.
How about these steps?
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.
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!
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.
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)
HasInstallProduct =
VAR orderNo = 'Sales Order'[Order No]
RETURN IF(
CALCULATE(
SUM('Sales Booking'[IsInstallProduct]),
'Sales Order'[Order No] = orderNo
) > 0, "Yes", "No")
Please can you share the workbook
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
could you pls provide the pbix file or the sample data in three tables ?
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |