How to Find Rows Missing from Table by Date

[BradonK]

Hi,

I am trying to create a report that takes two tables. One is a list of breathalyser readings by date. The other is a list of signatures on a form by date. Below is an example.

Breathalyser Data

Date	Name
23/10/2025	John Doe
23/10/2025	Jane Doe

Form Signatures

Date	Signatures
23/10/2025	John Doe
23/10/2025	Jane Doe
24/10/2025	John Doe

I am trying to find by date anyone who is on one list but not the other and display it as a table of names.

So, for this set of data, on the 24/10/2025, John Doe would be the only person on the list, as he is not present on the signatures list.

And for the 23/10/2025, the list would be empty as both people were on both lists.

The problem is I can't figure out how to make the relevant table to show this as a table visual.

Since the date is decided by a slicer visual, I can't just make a new query that filters by a certain date, it needs to be dynamic.

I am new to Power BI so not sure of my options here. Appreciate any help.

[v-achippa]

Hi @BradonK,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @danextian, @Arul, @Ahmedx, @Kedar_Pande for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Kedar_Pande]

Create a measure that compares the two tables for your selected date:

Missing Names =
VAR SelectedDate = MAX('DateTable'[Date])
RETURN
CONCATENATEX(
FILTER(
VALUES('Breathalyser Data'[Name]),
NOT(CALCULATE(
COUNTROWS('Form Signatures'),
'Form Signatures'[Date] = SelectedDate,
'Form Signatures'[Signatures] = EARLIER('Breathalyser Data'[Name])
))
),
[Name],
", "
)

 

Use this in a card visual, or modify to show in a table by removing CONCATENATEX.

[Ahmedx]

and try this

Signatura = 
 VAR _tbl = COUNTROWS(FILTER('Breathalyser Data','Breathalyser Data'[Name]='Form Signatures'[Signatures]&&'Breathalyser Data'[Date]='Form Signatures'[Date]))
RETURN
IF(ISBLANK(_tbl),'Form Signatures'[Date])

[Arul · Accepted solution]

@BradonK ,

try this in calculated column,

VAR a1 = EXCEPT(Signatures,Breathalyser)
VAR a2 = EXCEPT(Breathalyser,Signatures)
VAR output = UNION(a1,a2)
RETURN output

[BradonK]

Hi,

Thanks all for your answers and quick responses.

Apologies I did not get back to this sooner, I have had limited time to work on this.

@Arul your answer did exactly what I needed, returning a list of names that were on one list but not he other. Thanks again for your help.

Cheers

[danextian]

Hi @BradonK 

If the goal is to count records that exist in Signatures but not in Breathalyser, a measure using the EXCEPT function can return that difference directly.

Not in Breathalyser = 
VAR _sig =
    SUMMARIZE ( FormSignatures, FormSignatures[Date], FormSignatures[Signatures] )
VAR _breath =
    SUMMARIZE ( Breathalyser, Breathalyser[Date], Breathalyser[Name] )
RETURN
    COUNTROWS ( EXCEPT ( _sig, _breath ) )

However, if your goal is to check which name and date combination has a record in either table, you will need a dimension table for the names and another one for the dates and relate them to the fact tables.

Please see the attached pbix.