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