Exclude orders that contain a specific item

[robbinkees1]

Hi all,

I need some help in excluding orders that contain a specific item ( based on a slicer ). 

We have a sales master table and a sales lines table. 

So sales order 1 from the sales master table, has multiple items, so multiple itemlines in the sales lines table. 

I want to create a measure, which makes it possible to filter out orders that contain a specific item.

Based on a slicer, which is filled with the product codes that are available in the sales lines table. 

The end result should be a table with all our orders, except from the orders where one of the sales lines is matching the product code that is selected in the slicer. 

Can someone help me? 

Thanks in advance! 

[v-shex-msft · Accepted solution]

Hi @robbinkees1,

Currently, you cant use a slicer to achieve the selector effect which you mentioned.
For your requirement, I'd like to suggest you create a new table with all selected values which not have a relationship to the raw table and use it as a source of the slicer.

Then you can write a measure to check the current value and selected value to return flags and use it on the 'visual level filter' to achieve exclude filter effects.

Measure formula to filter selected records:

Exclude Flag =
VAR selected =
    VALUES ( NewTable[item] )
VAR currItem =
    SELECTEDVALUE ( Table[item] )
RETURN
    IF ( currItem IN selected, "N", "Y" )

Applying a measure filter in Power BI - SQLBI
Regards,

Xiaoxin Sheng

[amitchandak]

@robbinkees1 , if you want to exclude a selected item, you need an independent item table and measures like

measure =
var _items = allselected(Item[Item])
return
Countx(filter(summarize(Table, Table[Order], "_1", countX(filter(Table, Table[Item] in _items))), isblank([_1])), [Order])

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE