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

Helper II

## Filter Out or Tag a Sales Order with a Specific Item on it's Order Details

Hi everyone,

Been trying to learn new things with PowerBI lately, and I just hit a bump road with creating a measure or a calculation that would satisfy my current needs

So if if I have list of orders and their corresponding items in my datasource, in the example below.. how can I exclude a specific order with an ItemCode = Red or Blue so as the result I would get would just be Order-00003 which has the ItemCode Green on it?

TIA

1 ACCEPTED SOLUTION
Super User

new measure =

var _tab = summarize(filter(Table, Table[ItemCode] in {"Red", "Blue"}), [Order Code])

return

calculate(Sum(Table[Qty]), filter(Table, Table [Order Code] in _tab ))

use this with order number

5 REPLIES 5
Memorable Member

Hi,

1- My main Table is Shet193

2- Create the duplicated Table that is just included ItemCode (I named it Sheet193ItemCode)

3- I did create the measure in main table as below :

ExcluseActivity193 =
Var _SelectCnt = COUNT(Sheet193ItemCode[ItemCode])
Var _SelectIn = if( max(Sheet193[ItemCode]) in ALLSELECTED(Sheet193ItemCode[ItemCode]),1,0)
Return if(_SelectCnt <> 3,
if( max(Sheet193[ItemCode]) in ALLSELECTED(Sheet193ItemCode[ItemCode]),              1,              0),           0        )
4- Create Slicer on Sheet193ItemCode column :

5- Then create the Matrix on the original table and put the Filter that ExcludeActivity193 is 0 :

This is final Result :

Mahyartf
Helper II

Hi @MahyarTF was able to recreate what you've done, but one minor problem is that if I select LAB then only the line item for Labor shows up (if the Order has other items on it) I may not have mentioned this initially but if an order has items other than the filtered items, they should still appear in my list

Vice versa, if I uncheck the items that I want to filter out, the entire Order should also be filtered out including the other items on it

It was nice to see working it on a filter btw!

Memorable Member

Hi,

I think I missed that mentioned you need to delete duplicated records in Power Query for the new table.

It means you have to have 1 record for each value in the duplicated table.

Then do the rest :

Please Mark as a solution, if it helps you

Mahyartf
Super User

new measure =

var _tab = summarize(filter(Table, Table[ItemCode] in {"Red", "Blue"}), [Order Code])

return

calculate(Sum(Table[Qty]), filter(Table, Table [Order Code] in _tab ))

use this with order number

Helper II

Thanks and I was able to identify the items that I want to identify, but how do I go about filtering them?

Say if I only want to show all order records that has the item (but should also show all the items on that order and not just the item we added on the search)

vice versa, if I want to filter them out - they should also filter out all items on those order or basically the order should not show up

Ah ignore me! I was able to do the filter now and seems to be working fine! but would love to know if it's possible to set the items as variable list and do filter by items using a slicer visual - although atm it works as intended.. thank you

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.