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

     

     

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