Way to return list of IDs minus those in another table via filter (rather than refresh)?

[mjmeyervorys]

Hello!

I have two tables that both contain unique IDs. One of these is the 'master' list (Let's just say it's one column "ID"), the other is a list of particular IDs and the associated users that should NOT be able to see them (a 'denial' list, let's go with "ID" and "USERNAME").

I'm trying to figure out a way where, when selecting a user via a filter on a 'user' table, Power Bi can return all ID in the 'master' that do NOT exist in the 'denial' table for that USERNAME. Basically an =Except command, but one that would not just run at refresh (unless there's a way to force a refresh after a filter selection in which case I'd just build a calculated table off of the selected value). Is this possible?

I am not able to do the reverse (an 'access' table) as with the number of USERNAME and number of ID it would result in an enormous multi-billion row table. I'm hoping it's possible using this 'denial' table I have.

Just in case I've been unclear (been working on this problem for a while), I have:

'Master'

ID

1

2

3

4

5

'Denial'

ID USERNAME

2 user1

3 user2

5 user2

'Usernames'

USERNAME

user1

user2

For user1 it should return IDs 1,3,4,5 (skipping 2). For user2 it should return IDs 1,2,4. If there was a user3 it shoudl return all.

Thanks in advance!

[Mariusz · Accepted solution]

Hi @mjmeyervorys 

Try this measure, and see the attached file for a solution.

Measure = 
CALCULATE(
    COUNTROWS( Master ),
    EXCEPT(
        Master,
        VALUES( Denial[ID] )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn