Exclude rows from being filtered out of table.

[Marty_Mcfly]

Hello,

So I have a business request to make it so the CEO can't be filtered out of table. In the example below I want to be able to select any business org and have the CEO still show up. For example, if I select Business Org 2 or Business Org 3, I still want the CEO to showup. Is this possible in Power Bi?

[ryan_mayu · Accepted solution]

@Marty_Mcfly 

you can try to create a new org table to filter

Table 2 = DISTINCT('Table'[Business Org])

then create a measure

Measure = if(max('Table'[Business Org])=SELECTEDVALUE('Table 2'[Business Org])||max('Table'[Job Title])="CEO",1)

add this measure to visual filter and set to 1

if you want to do the multi-selection, you can change the measure to below.

Measure =

 VAR list=CONCATENATEx('Table 2','Table 2'[Business Org])

 return if(CONTAINSSTRING(list,max('Table'[Business Org]))||max('Table'[Job Title])="CEO",1)

pls see the attachment below

[danextian · Accepted solution]

Hi @Marty_Mcfly 

You can use a disconnected table visually filter your viz. The below calculated table simply returns the distinct category but is not related to any other tables.

DisconnectedCategory = 
DISTINCT ( Category[Category] )

The CategoryFilter propagates the virtual relationship between the disconnected and connected category table.

CategoryFilter = 
VAR _apparel = 
    ROW ( "Category", "Apparel" ) 
-- Creates a single-row table with a column named "Category" and a single value, "Apparel". 
-- This is used later to include "Apparel" explicitly in the filter logic.

RETURN 
    CALCULATE ( 
        COUNTROWS ( Category ), 
        -- Counts the rows in the 'Category' table while applying the filters defined below.

        KEEPFILTERS ( 
            TREATAS ( 
                UNION ( VALUES ( DisconnectedCategory[Category] ), _apparel ), 
                -- Combines the values from the 'DisconnectedCategory[Category]' column 
                -- with the single value "Apparel" from the `_apparel` variable.
                
                Category[Category] 
                -- Maps the resulting values to the 'Category[Category]' column 
                -- in the 'Category' table to apply them as filters.
            )
        )
        -- Ensures that any existing filters on the 'Category[Category]' column 
        -- are preserved while applying the new filters from the TREATAS function.
    )

Apparel is hidden from the slicer using the visual filter but will always be selected (see CategoryFilter column).

Alternatively, you can materialize Apparel for every category row in Power Query

But since the table with extra rows won't include unique values anymore and the relationships always flow from one to the many side a single direction relationship, you'll have to make use of a bi-directional relationship.

The second approach is easier to maintain as you can use the Category2 column to directly filter related tables without the need to create a new measure as a visual filter but this may cause a performance issue on large tables. The difference betwen the two approaches is negligible on small tables.

Please see attached sample pbix.

[ryan_mayu · Accepted solution]

@Marty_Mcfly 

you can try to create a new org table to filter

Table 2 = DISTINCT('Table'[Business Org])

then create a measure

Measure = if(max('Table'[Business Org])=SELECTEDVALUE('Table 2'[Business Org])||max('Table'[Job Title])="CEO",1)

add this measure to visual filter and set to 1

if you want to do the multi-selection, you can change the measure to below.

Measure =

 VAR list=CONCATENATEx('Table 2','Table 2'[Business Org])

 return if(CONTAINSSTRING(list,max('Table'[Business Org]))||max('Table'[Job Title])="CEO",1)

pls see the attachment below