cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donaldm
Helper III
Helper III

Exclude items from a filtered list

I have a master table of person's names and I have a separate table of individuals I want to exclude from the master list.

 

Rather than removing the individuals one by one from the visual/page/report filters, is there another method to this?

 

1 ACCEPTED SOLUTION

Found 2 methods doing so:

1) Except(SelectColumns(MasterTable, "XXX", MasterTabl[Name]), SelectColumns(ListToExclude, "YYY", ListToExclude[Name]))

 

2) Anti Join using merge queries

 

thanks!

View solution in original post

6 REPLIES 6
AlB
Super User III
Super User III

Hi  @donaldm

 

Two options:

1. Create a calculated table like this:

 

NewMasterTable =
CALCULATETABLE (
    MasterTable,
    EXCEPT ( DISTINCT ( MasterTable[Name] ), ListToExclude )
)

and use it as new master table. 'ListToExclude' is the one-column table with the names to be excluded. 

 

2. a) Create a one-column table with the list of names to keep:

 

 

FilterColumn =
EXCEPT ( DISTINCT ( MasterTable[Name] ), ListToExclude )

b) Place FilterColumn[Name] in Page level filters and select Advanced Filtering --> Show items when the value is:  Not blank 

 

 

Looks like EXCEPT only works if the 2 tables (MasterTable & ListToExclude) have the same dimensions.

 

My MasterTable has 20+ columns with all sorts of information, and List ToExclude is 1 column.  Is there an approach using Parameters?

AlB
Super User III
Super User III

@donaldm

 ??

The  two tables in the EXCEPT in the code above are one-column tables:

     EXCEPT ( DISTINCT ( MasterTable[Name] ), ListToExclude )

The number of columns in MasterTable is irrelevant.

This is the error I am having:

 

 

A single value for column 'name' in table 'MasterTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

AlB
Super User III
Super User III

 

Neither of the options suggested above are measures. They're both calculated tables as indicated  

Found 2 methods doing so:

1) Except(SelectColumns(MasterTable, "XXX", MasterTabl[Name]), SelectColumns(ListToExclude, "YYY", ListToExclude[Name]))

 

2) Anti Join using merge queries

 

thanks!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors