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

[mangchaaBI]

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

[MahyarTF]

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 :

Appreciate your Kudos

[mangchaaBI]

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! 

[MahyarTF]

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 :

Appreciate your Kudos.

Please Mark as a solution, if it helps you

[amitchandak · Accepted solution]

@mangchaaBI ,

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

[mangchaaBI]

Hi @amitchandak 

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