Comparing different tables to show which doesn't have an associated record

[Daveed1973]

I have a table in Power BI desktop which lists a document name and whether that document has been signed by an individual which will look like this.

SignedDocsTable

DocName    UserName

Doc1            User1

Doc2            User1

Doc3            User2

Doc1            User2

I have another two tables, one which has a list of all Documents and one which has a list of all Users, like this.

DocumentsTable

DocName

Doc1

Doc2

Doc3

UserTable

UserName

User1

User2

User3

From this I need to create a list of all documents which haven't been signed by users. So if User1 hasn't signed Doc3 then another table needs to be generated to show this. Using the example tables above the resultant table would look like this

TableUnsignedDocs

Doc    User

Doc1  User3

Doc2  User2

Doc2  User3

Doc3  User1

Doc3  User3

So almost like an anti many to many relationship. Any ideas on how to do this would be appreciated.

[Anonymous · Accepted solution]

It's easiest to do this in DAX with a calculated table:

DAX Table= 
VAR AllCombos = CROSSJOIN(AllDocs, AllUsers)
return
EXCEPT(AllCombos, Signed) 

Here's another solution that uses Query Editor.  I can't attach the file, but here's a quick explanation of what I've done, and the Custom M code to generate the tables (copy and paste this in the Advanced Editor):

AllCombinations Table
let
    Source = AllUsers,
    #"Add All Docs" = Table.AddColumn(Source, "DocName", each AllDocs[DocName]),
    #"Expanded DocName" = Table.ExpandListColumn(#"Add All Docs", "DocName")
in
    #"Expanded DocName"

UsersWithUnsignedDocs Table
let
    Source = Table.NestedJoin(AllCombinations,{"User Name", "DocName"},Signed,{" UserName KEY", "DocName KEY"},"Signed",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Signed"})
in
    #"Removed Columns"

I created an intermediate table called "AllCombinations".  This table is essentially a crossjoin of all users and all documents.

This is done by referencing the AllUsers table, and creating a custom called where each value is the list of all distinct DocName values from the AllDocs table.  I then expand each list out in rows to give the full table (9 rows in this example).

I then created a new table "UsersWithUnsignedDocs", by doing a Merge Queries as New step.  I joined the Signed and AllCombinations tables on both columns (UserName and DocName), and used a Left Anti Join (only keeping records that exist in AllCombinations but NOT in Signed).  Remove the nested column, and you're all set!

I recommend the DAX method.  It should be faster if your dataset is larger.

[Daveed1973]

Thanks for your help on this. I have done it a slightly different way but you have pointed me in the right direction with the CROSSJOIN.

So I created the crossjoined tablke with all the possible options and then used a FILTER and EARLIER calculated column which compared the two tables and gave me a true or false depending on if there was a match or not. So something like this...

DocSigned =
CALCULATE (
    COUNTROWS ( 'SignaturesTbl' ),
    FILTER (
        'SignaturesTbl',
        'SignaturesTbl'[Signature]
            = EARLIER ( 'AllCombos'[FullName] )
            && 'SignaturesTbl'[DocTitle] = EARLIER ( 'AllCombos'[DocTitle] )
    )
)
    > 0

I can then display in my report all the docs which have been signed in one detailed table and all those that haven't.