Count based on a lookup with multiple criteria - DAX

[ajpaul]

Hi,

I have a table of data which contains two columns.  One is FPItemID which details the finished product item ID and the other is RMItemID which details the raw material item id that goes into making this finished product.

An example dataset is below:

FPItemID	RMItemID
10	1001
10	1002
10	1003
10	1004
10	1005
10	1006
10	1007
10	1008
10	1009
10	1010
10	1011
20	1001
20	1002
20	1003
20	1004
20	1005
20	1006
20	1011
20	1012
20	1013
20	1014
20	1015
30	1001
30	1003
30	1004
30	1005
30	1006
30	1007
30	1008
30	1009
30	1019
30	1020
30	1021
30	1022

I am relatively new to DAX but am struggling to create the solution I am looking for.  I would like to be able to select a finished product ID and then compare the raw material ID associated with that finished product ID against all the other raw materials ID associated with the other finished product IDs.  The idea being that it would return a list of finished product ids and a count of common raw materials IDs that they share.  The ultimate aim would also be to see which raw material IDs were common accross the finished product IDs.

For example in the dataset above if I selected FPItemID 10 i would want to see that there are 7 common raw material IDs in FPitem 20 and 8 common raw material IDs with FPItem 30.  Again if it is possible to see which raw material item IDs are common as well that would be great.

Thanks for the help with this.

AJ

[lbendlin · Accepted solution]

Would you be satisfied with a visual solution?  Create a Matrix visual, put the products in the columns and the raw material in the rows. Then put the raw materials in the values and change the aggregation to count.

[ajpaul]

I never even considered this option!

Thanks a lot for your help.

AJ