Measure to perform gap analysis between 2 lists from 2 tables

[Matt22365]

Hi

I am trying to work out a measure to perform a gap analysis between 2 columns in different tables.

e.g. I have a reference stock list in one table (which shows what I need) and a actual stock list in a different table. What I would like is a numeric value output showing how many items I am missing from stock

These tables are related. (rough example of the 2 columns below) 

Reference List	Item of Interest
apples	apples
oranges
bananas	bananas
pears
peaches	peaches

Using this example, I would want the output to be 2. i.e. I am missing 2 items of stock

Can someone help me work out what the DAX formula would be to calculate this?

Thanks

Matt 

[v-xuding-msft]

Hi @Matt22365 ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft · Accepted solution]

Hi @Matt22365 ,

I created a sample that you could reference to have a try.

Measure = 
CALCULATE (
    MAX ( 'reference stock list'[Reference item list ] ),
    FILTER (
        'reference stock list',
        NOT (
            'reference stock list'[Reference item list ]
                IN VALUES ( 'actual stock list'[Item] )
        )
    )
)

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Matt22365]

Hi

This worked great, thank you

Matt