The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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.
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.
Hi
This worked great, thank you
Matt