Forum Discussion

BradonK's avatar
BradonK
Frequent Visitor
9 months ago
Solved

How to Find Rows Missing from Table by Date

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

DateName
23/10/2025John Doe
23/10/2025Jane Doe

 

Form Signatures

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

Need Help
  • Arul's avatar
    Arul
    9 months ago

    BradonK ,

    try this in calculated column,

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

6 Replies

  • Arul's avatar
    Arul
    Super User
    9 months ago

    BradonK ,

    try this in calculated column,

    VAR a1 = EXCEPT(Signatures,Breathalyser)
VAR a2 = EXCEPT(Breathalyser,Signatures)
VAR output = UNION(a1,a2)
RETURN output
    • BradonK's avatar
      BradonK
      Frequent Visitor
      9 months ago

      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

  • Ahmedx's avatar
    Ahmedx
    Super User
    9 months ago

    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])

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    9 months ago

    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.

     
     
  • v-achippa's avatar
    v-achippa
    Community Support
    9 months ago

    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

  • danextian's avatar
    danextian
    Super User
    9 months ago

    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.