Compare 2 Columns

[datadmin-austin]

Hello All,

We run safety audits daily and I have 2 different SharePoint Lists. One is just building details and the other are audit score details, but they both have building names on them. I am trying to see which buildings have not been audited. Here is an example of the data:

Table1:

BuildingNameColumn

Buildng 1

Building 2

Building 3

Building 4

Building 5

Building 6

Table 2

BuildingNameColumn             AuditScorePercentage      Date

Buildng 1                                                95.00%               3/23/2022

Buildng 3                                                95.00%               3/15/2022

Buildng 5                                                95.00%               3/082022

Desired Result

BuildingNameColumn             AuditScorePercentage      Date

Buildng 1                                                95.00%               3/23/2022

Buildng 2                                                -

Buildng 3                                                95.00%               3/15/2022

Buildng 4                                                -

Buildng 5                                                95.00%               3/082022

Buildng 6                                                -

Can some help me achieve this or advice?

Thank you very much!!

[DataInsights · Accepted solution]

@datadmin-austin,

Try these measures:

Audit Score Percentage = MAX ( Table2[AuditScorePercentage] )

Audit Date = MAX ( Table2[Date] )

In the visual, use Table1[BuildingNameColumn] and enable "Show items with no data". Add the measures above to the visual.

[datadmin-austin]

@DataInsights That worked! I kept missing the "Show items with no data" option. Thank you for the details and visuals!

[DataInsights]

You'll need a relationship between the tables. Crossfilter direction is "Both" since it's a 1:1 relationship. This can be changed to 1:* if desired ("Single" crossfilter direction).