How to have one option in a filter to select all values

[Anonymous]

So I have a dataset of accounts and each account is within a region, I also have an associated file that has the following regional structure of APAC, Americas, Europe and Intl. Anything that has an Intl region tag applies to all of APAC, americas and europe, what I want is when I filter on say APAC it filters to show both APAC and Intl for both the account file and the associated file, and if you filter on Intl it displays all values.

I am currently using a region filter table to link the two tables and I have tried to implement region encoding on this table on the different regions but I havent been able to execute it correctly.

Thanks for your help.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Based on my assumption, I created two sample tables: Accounts table and Regions table

 

Please create a calculated table with below formula:

Regions table_1 =
UNION (
    'Regions table',
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Regions table'[AccountID] ),
            FILTER ( 'Regions table', 'Regions table'[Region] <> "Intl" )
        ),
        "Regional", "Intl"
    ),
    CROSSJOIN (
        CALCULATETABLE (
            VALUES ( 'Regions table'[AccountID] ),
            FILTER ( 'Regions table', 'Regions table'[Region] = "Intl" )
        ),
        CALCULATETABLE (
            VALUES ( 'Regions table'[Region] ),
            FILTER ( 'Regions table', 'Regions table'[Region] <> "Intl" )
        )
    )
)

Also, you need to add a calculated column in above table:

Region in origina table =
LOOKUPVALUE (
    'Regions table'[Region],
    'Regions table'[AccountID], 'Regions table_1'[AccountID]
)

Then, you will get below result.

Establish a one to many relationship from 'Accounts table' to 'Regions table_1'.

Add 'Regions table_1'[Region] into slicer. Drag corresponding fields from 'Regions table_1' and 'Accounts table' into table visual.

Best regards,

Yuliana Gu

[Anonymous]

That looks like exactly what I was looking for thank you!