Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |