Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lennox25
Helper V
Helper V

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.

lennox25_0-1702385247834.png

lennox25_1-1702385261545.png

 

 

1 ACCEPTED SOLUTION
Bibiano_Geraldo
Resolver I
Resolver I

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

 

Bibiano_Geraldo_1-1702390482130.png

 

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@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"
    )
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Bibiano_Geraldo
Resolver I
Resolver I

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

 

Bibiano_Geraldo_1-1702390482130.png

 

Thank you - your brilliant!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.