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 c...
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.

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.

    • BarryFletcher's avatar
      BarryFletcher
      Helper III
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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!