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