Reverse filtering - Find all values from a column by filtering one from another

[Anonymous]

Hello everyone, 

I have an odd request but hear me out. 

So my company has this table and wants to filter by account to find out who the manager of that account is. All simple so far. 

They would like to see not only the manager for that account but all the accounts the manager is responsible for (in a table).

I would like to know if there is a solution for this first. So if I filter BBC, it would show up LG & Dolby as well, since John is a Manager to all of them:

Account	Manager
BBC	John
LG	John
Samsung	Gabby
Dolby	John
Tata	Danny

And the next level of complexity is to show up the account filtered & All the Accounts that have the same manager & All that have Global Coverage. 

Account	Manager	Coverage		Filter: BBC
BBC	John	Australia
LG	John	Global		Account	Manager	Coverage
Samsung	Gabby	Japan		BBC	John	Australia
Dolby	John	Japan		LG	John	Global
Tata	Danny	Global

Is there a possibility to do this? 

Thank you very much for your support, 

Ovidiu

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution.

First you have to have the acount names in a seperate disconnect table then create "FiltereMeasure", place it the filter pane of the table visual and select "is not blank" then apply the filter.

FilterMeasure = 
VAR SelectedAccounts = VALUES ( 'Acount Names'[Account] )
VAR SelectedManagers =
    CALCULATETABLE ( 
        VALUES ( 'Acount Details'[Manager] ), 
        'Acount Details'[Account] IN SelectedAccounts,
        ALL ( 'Acount Details' )
    )
VAR SelectedCoverages =
    UNION ( 
        CALCULATETABLE ( 
            VALUES ( 'Acount Details'[Coverage] ), 
            'Acount Details'[Account] IN SelectedAccounts,
            ALL ( 'Acount Details' )
        ),
        { "Global" }
    )
VAR FilteredTable = 
    FILTER ( 
        'Acount Details',
        'Acount Details'[Manager] IN SelectedManagers
            && 'Acount Details'[Coverage] In SelectedCoverages
    )
RETURN
    IF ( NOT ISEMPTY ( FilteredTable ), 1 )

[Anonymous]

Thank you very much for this! You've made a lot of people happy! 

It worked perfectly and exactly as I wanted! 

Wish you a great day and Happy Holidays! 

[djurecicK2]

Hi @Anonymous ,

 I think this would be difficult to do inside of a single visual, but might be a good use case for a drillthrough to another page that shows all of the manager related information. 

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough