Compare two tables but by two columns; Employee ID and Training Topic Title

[victoria_g06]

I need some help with logic please and it might just be formatting! 

I have a list of training documents and I need to find what documents someone hasn't completed. 

I have the data on two different tables, the Name in the below table is from the main list of documents and the count is from the completed list, DocumentTemplateId is in both but Employee is only in the completed list. 

This is the DAX for the documents completed

Total Documents Completed = 
SUMX(
    SUMMARIZE(
        SiteDocsFormTypes,
        SiteDocsFormTypes[DocumentTemplateId],
        "TotalCount", COUNTAX(SiteDocsForms, SiteDocsForms[DocumentTemplateId])
    ),
    [TotalCount]
)

[Arul]

@victoria_g06 ,

Could you provide sample of both the tables?

[victoria_g06]

Hi Arul, 

It wont let me post tables as it has HTML?  Please use below data

CreatedBy 

04229bf9-2162-4040-bd49-c277562ab84b

DocumentName 

PRESS - Hydraulic

DocumentTemplateId

01ce95f9-6572-425e-a293-82ebbb50009c

DocumentTemplateId

00cb727c-c51a-4c47-a6d2-ff97cc54c69c

Name

No. 44 - Vehicle starting and moving 000000

[Anonymous]

Hi @victoria_g06 ,

I am assuming that the employee needs to complete all the training in the 'main list of documents' table, so try this measure:

List of unfinished = 
IF (
    HASONEVALUE ( 'Table2'[Name] ),
    CONCATENATEX (
        EXCEPT (
            DISTINCT ( 'Table1'[DocumentName] ),
            CALCULATETABLE (
                VALUES ( 'Table1'[DocumentName] ),
                TREATAS (
                    DISTINCT ( 'Table2'[DocumentTemplateId] ),
                    'Table1'[DocumentTemplateId]
                )
            )
        ),
        'Table1'[DocumentName],
        ","
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[victoria_g06 · Accepted solution]

Thank you for looking into the issue Gao, I wasn't really after a list in the one cell as some people might have hundreds not completed. 

I wanted to have the list of document names in the rows and count of times completed in one Value column and then the next column a number for not completed. So there will be some blanks or 0's in each column depending if that document name/ number has been completed.

I think I found a workaround with DAX in that I've just added the names of the documents into the matrix and then the DAX of the ones completed. 

It was just hard to wrap my head around it yesterday!