Compare 2 Tables

[BarryFletcher]

Hi all,

I have seen some posts regarding comparing tables but I think this might have a simpler solution if you don;t mind helping out?

I have two tables, Table A has a list of staff whicch currently has a total of 184 rows.

I also have a table of timesheets submitted (per week) and I'd like to be able to select a work week from a slicer (Week 2 for example) and be able to say that 50 timesheets were submitted and get a list of the name that do not have a timesheet submitted?

So on the dashboard below - I'd like another table listing the peopl who have not completed a timesheet during the workweek selected in the slicer in the top left hand corner?

[Anonymous · Accepted solution]

Hi @BarryFletcher , hello bhanu_gautam, thank you for your prompt reply!

We could use the following measure to check the missing subbmitted staff:

MissingFlag = 
IF(
    NOT (
        MAX('Staff List'[Staff Name]) IN 
        CALCULATETABLE(
            VALUES('Timesheets'[Staff Name]),
            'Timesheets'[Work Week] = SELECTEDVALUE('Timesheets'[Work Week])
        )
    ),
    1,
    0
)

Then filter the staff table visual with MissingFlag=1:

Sample test for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarryFletcher]

Apologies - permssions changed

[Anonymous · Accepted solution]

Hi @BarryFletcher ，

Based on your data, switch to this measure:

MissingFlag = 
VAR SubmittedUserName=SUMMARIZE(
            FILTER(ALLSELECTED('Timesheet Hours'),'Timesheet Hours'[NewWorkWeek] = SELECTEDVALUE('Timesheet Hours'[NewWorkWeek])),'Timesheet Hours'[New Staff Name]
        )
VAR TAG=IF(
        NOT (
            CONTAINSSTRING ( CONCATENATEX ( SubmittedUserName, 'Timesheet Hours'[New Staff Name], "," ),
                TRIM(MAX('BLS_Report'[New Staff Name]))
            )
        ),
        1,
        0
    )
RETURN TAG

 For more detailed information, please check the attachment below.

[BarryFletcher]

Thank you - that worked perfectly

Barry

[BarryFletcher]

Apologies, permissions updated 

[BarryFletcher]

s for the quick replies but I don't see any difference between the Staff List and the Submitted tables - I have shared my BI if you don't mind a sanity check as to what I'm doing wrong? Appreciate all the help

https://drive.google.com/file/d/1E7hccdP7xPaea5id-n_3UcrzZtoJiUa7/view?usp=drive_link 

[BarryFletcher]

apologies - I hadn't seen that attachment

[bhanu_gautam]

@BarryFletcher  First 

Create a measure to count submitted timesheets:

SubmittedTimesheets = COUNTROWS(Timesheets)

Go to the "Model" view and create a relationship between the Staff table and the Timesheets table using the StaffID column.

Create a measure to identify staff without timesheets:

StaffWithoutTimesheets =
CALCULATETABLE(
VALUES(Staff[StaffName]),
NOT(
EXISTS(
Timesheets,
Timesheets[StaffID] = Staff[StaffID]
)
)
)

Add a table visual to your report and use the StaffWithoutTimesheets measure to display the names of staff who have not submitted a timesheet for the selected week.

[BarryFletcher]

Thanks for the quick reply but I am getting this error

[Anonymous · Accepted solution]

Hi @BarryFletcher , hello bhanu_gautam, thank you for your prompt reply!

We could use the following measure to check the missing subbmitted staff:

MissingFlag = 
IF(
    NOT (
        MAX('Staff List'[Staff Name]) IN 
        CALCULATETABLE(
            VALUES('Timesheets'[Staff Name]),
            'Timesheets'[Work Week] = SELECTEDVALUE('Timesheets'[Work Week])
        )
    ),
    1,
    0
)

Then filter the staff table visual with MissingFlag=1:

Sample test for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarryFletcher]

Hi Joyce,

Could you elaborate on the table you've shown:

I have the measure created, could you elaborate on how to apply and to what table please?

[Anonymous]

Hi @BarryFletcher , thank you for your feedback.

You could check the sample pbix file above for details.

If it is not feasible for you, please feel free to reply!

[BarryFletcher]

Hi there,

Looking at the sample BI - the No submitted staff table doesn;t update regardless of which week number that is selected in the slicer? The  only name that stays listed is "Jack"

[Anonymous]

Hi @BarryFletcher, 

The reason Jack has been listed in the ‘No Submitted Staff’ table is because he doesn't have a submission record for any week.

If I add a new row to the Submissions table, the corresponding data is as follows.

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarryFletcher]

Thanks for teh quick replies - I am still not seeing the tables change based on the slicer selection.

Sample of the BI here:

https://drive.google.com/file/d/1E7hccdP7xPaea5id-n_3UcrzZtoJiUa7/view?usp=drive_link

[Anonymous]

Hi @BarryFletcher,

Access to this link is denied and I could not check your file.