Display items that do not have record on a specific day

Hi all. Suppose I have 2 tables in MySQL database, Device Info and Operation Record.

For Device Info:
|----1----|–Device 1–|
|----2----|–Device 2–|
|----3----|–Device 3–|

For Operation Record:

When imported into QuickSight, suppose I applied a date filter and use Gauge Chart by placing counts in Operation Record as value and counts in Device Info as total value, when date = 01-07-23, I should get 100%. when date = 02-07-23 I should get 33.33%.

But with the date filter = 02-07-23 applied, how do I display the name of those devices that do not have record in the Operation Record table? The the result produced by the following SQL query is what I am trying to get in QuickSight:

SELECT Name FROM Device Info WHERE id NOT IN (SELECT a.id FROM Device Info a JOIN Operation Record b ON a.id = b.id WHERE Date = “02-07-23”);

Would appreciate it for syntax for calculated field and idea on proper visual type to display the information. Thank you very much.

Hi @Lauris ,

You can achieve the 1st by using a distincountover

For the 2nd, I dont think it’s possible directly from the analysis.

Kind regards,