Forum Discussion

BarryFletcher's avatar
BarryFletcher
Helper III
1 year ago
Solved

Compare 2 Tables

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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

15 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      BarryFletcher
      Helper III
      1 year ago

      Thanks for the quick reply but I am getting this error

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.