Use filter to exclude

[mahimabedi]

Is there a way use a filter to exclude rows?

Example: on selecting "productA" in a filter, i get all products EXCEPT "productA"

Can we create a measure to return  a list or table where the selected value is removed?

[konstantinos · Accepted solution]

@mahimabedi  You need to add to the table an index row. If you don't have, create an index in query mode when you load the table.

On the visual table just add the index column, make sure the defaut calculation for index  is 'Do not summarise' on the values as usually will show sum 

[Anonymous]

there is a new function in powerbi.

but you have to create the table using the visual "Matrix"

when you right click on a row you would like to exclude, there should be an "exclude" on the pop up menu.

[jahida]

Hey,

There is a way to do it, but it's not trivial. Measures can't return lists or tables, they have to evaluate to values, so the measure you described isn't possible. You can write measures to do aggregations based on NOT "productA". Here's an example:

CountSomethingMeasure =
SUMX(SUMMARIZE(Table, Table[Product]), COUNTROWS(FILTER(CALCULATETABLE(Table, ALLEXCEPT(Table, Table[SomeOtherFilter])), Table[Product] <> EARLIER(Table[Product])))) / IF(DISTINCTCOUNT(Table[Product]) = CALCULATE(DISTINCTCOUNT(Table[Product]), ALL(Table[Product])), MAX(DISTINCTCOUNT(Table[Product]) - 1, 1), 1)

SomeOtherFilter is there just to show how you could possibly keep other filters that are applied eg. if you want to filter by Location and by not product, you could change [SomeOtherFilter] to [Location].

That would give you the number of rows in a Table that are not associated with the selected product. If nothing is selected, it would give the total number of rows. It does not support selecting more than one product to omit (let me know if that functionality is important and I can give it a shot).

[konstantinos]

@jahida @mahimabedi   Really interesting..I was trying to test EXCEPT() for a long time & now is the time..

I create a table 'Sales' 

ProductAmount

A	5
A	5
A	5
B	5
B	5
B	5
C	5
C	5

a table 'Products'

Product

A

B

C

Then create the relantionship ( one way ) and the formula

Sales = CALCULATE(SUM(Sales[Amount]);EXCEPT(ALL(Products);Products))

Result :  

* You need to use the column from sales table, else if use the column from Product it gives you the correct sum but shows the selected product.

[SAIKIRANCHAVALI]

Sales = CALCULATE(SUM(Sales[Amount]);EXCEPT(ALL(Products);Products))

i am using same its not working i my power bi. can you suggest me. 

[jahida]

Much better than my solution, well done.

[v-qiuyu-msft · Accepted solution]

Hi @mahimabedi,

In addition, with the help of the Query Parameter, you can also achieve your requirement. When you run the report, you can change prefer parameter value to filter corresponding data rows, instead of setting any report filter or slicer.

In your scenario, you can create a query parameter and list all products. Then set the filter with "does not equal" in Query Editor

like below:

For more information, you can take a look at this article: Deep Dive into Query Parameters and Power BI Templates.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

I tried to get to this step but I coouldn't get to the filter pop up window. Could you explain how you got here?

[Anonymous]

Is there a way I can do this in DIrect Query mode, this method can only be done in import mode.

[konstantinos]

@jahida Yours also works with previous DAX versions, mine not 🙂

[mahimabedi]

Thats sweet and simple! But how do i return a table instaed of a calculation

Is there a way to return a subset of a table but using a filter to exclude rows.

example if i have table 1

A	20
B	20
A	30
C	10
B	40
C	10

On using a slicer- when i select "A"- it should return a table 

B	20
C	10
B	40
C	10

[AK1729]

I have a similar requirement in my current assignment.

Ive designed a bar graph & table summary visualizations by importing a model(table) from azure db source.

some columns in the model were used as slicers on the report page.

on a particular column selection i.e., slicer value selection ; I need both the visualizations(bar graph & table summary) to be refreshed with the corresponding data having all the column values except the slicer value selection that I made above.

could you please help me on this!

*TIA* 

[konstantinos · Accepted solution]

@mahimabedi  You need to add to the table an index row. If you don't have, create an index in query mode when you load the table.

On the visual table just add the index column, make sure the defaut calculation for index  is 'Do not summarise' on the values as usually will show sum 

[Jesus]

Why an index?

The same issue here:

https://community.powerbi.com/t5/Desktop/Including-rows-excluded-in-a-other-filter-in-a-report/m-p/5...