Forum Discussion

lennox25's avatar
lennox25
Post Patron
2 years ago
Solved

How to highlight differences between two tables -showing data thats on one table and not on another

I have two tables not related. I need to highlight any missing dates between both tables. This example only shows dates missing on the second table but for other stores dates could be missing from the first table. In this example I would like the missing dates from Table 2 highlighted in table 1. So 10/04/23, 16/04/23 and 23/04/23 would be highlighted.

 

 

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    2 years ago

    Try the following code:
    1- In the first table put the following code:

    Column=
    VAR data = 'Your_Current_Table'[Date]
    VAR result =
    LOOKUPVALUE(
        'Your_Second_Table'[Date],
        'Your_Second_Table'[Date],
        'Your_Current_Table'[Date]
        ) = data
    RETURN
    result
     
    2- Create a measure with the following code:
    Measure= SUM('Your_Current_Table'[Column])
     
    3- In your matrix, format conditionaly your values as shown bellow. Make sure that your based field is the measure created before

     

     

3 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    2 years ago

    Try the following code:
    1- In the first table put the following code:

    Column=
    VAR data = 'Your_Current_Table'[Date]
    VAR result =
    LOOKUPVALUE(
        'Your_Second_Table'[Date],
        'Your_Second_Table'[Date],
        'Your_Current_Table'[Date]
        ) = data
    RETURN
    result
     
    2- Create a measure with the following code:
    Measure= SUM('Your_Current_Table'[Column])
     
    3- In your matrix, format conditionaly your values as shown bellow. Make sure that your based field is the measure created before

     

     

  • smpa01's avatar
    smpa01
    Community Champion
    2 years ago

    lennox25  you can write a measure like this

    Measure= 
VAR _left =
    { MAX ( t1[Date] ) }
VAR _RIGHT =
    GENERATE ( _left, VAR ky = [Value] RETURN FILTER ( t2, t2[Date] = ky ) )
RETURN
    IF (
        COUNTROWS ( _RIGHT ) == BLANK (),
        "missing in 2nd table",
        "not missing in 2nd table"
    )