New Column with reference to second table with two filters

[shellznz]

Hi guys.   New to the board, so please forgive me if I'm in the wrong place.

I have two tables:

Table 1 Basic information - Client Name, Client Number, Job Number

Table Two list clients and/or jobs that are marked as confidential.  The trick is how to determine whether the confidential mark is against a job, or a client.    If the row has a number in both client number AND job number, then it should be assumed that it is a confidential JOB.   If the row has a number in client only, and the job field is empty, then the confidential lock is across the whole client, not just the individual job.

I need to filter out clients displaying in Table 1, to exclude any clients that have a CLIENT lock, not a JOB lock.   I have managed to get a filter working by creating a new Column in Table 1 called ClientLock - it compares if the client number is in the lock list from Table 2, and can therefore be filtered out.  However, it does this even if there is a job number assigned to the row, which would signal it is only a job lock, not a client lock.  Here's the DAX formula I have:

ClientLock = CALCULATE(COUNTROWS(table2), FILTER(table2, table2[cltnum]='table1'[cltnum])) > 0

This gives me a true/false result, which I can apply to a table1 filter.

But I need to add a second filter somewhere that only does this comparison against filtered rows in table2[jobnum] that are empty.

Any suggestions?

[amitchandak]

@shellznz ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[shellznz]

Thanks Amitchandak.  Below is a link to a folder with two Excel files and the tables that we have.  Please let me know if you have any problems accessing

Link to table files 

The Client Info example is the table that I want to display.   But it needs to be filtered and have any clients that are marked as confidential removed from view.   The clients in this example are client numbers 12 and 16 that should not be displaying.

The Confidential table is where the client and job numbers that are marked as confidential are recorded.  You will see there are multiple items for the same client number - could be that there are multiple jobs for the client, and even with each job there could be multiple entries, it all depends on the users that are allowed access to that job (one entry per authorised user).

If a row has a client number only, with no job numbers, then it is safe to assume that the entire CLIENT file is locked down as confidential.  

If the row has both a client and a job number in it, then the job number ONLY is locked down.  We will use this same logic to create a job list with similar filter for a Job Summary report as well.

Does that all make sense?!