Calculated column to show duplicate/common items and hide uncommon items

[trdoan]

Hi everyone,

Here is my table called "Data":

Vendor	Size Group	Model	Product Number	Quantity	Cost	TAT	Posting Date
A	S	A150	5	150	450	67	July 7, 2018
A	M	A200	5	250	1500	75	June 22, 2018
A	M	A150	5	25	8500	85	July 9, 2018
C	L	A200	10	350	1250	125	March 5, 2018
C	XL	A500	5	150	6500	45	February 20, 2018
A	M	A900	10	385	475	40	January 29, 2018
A	M	A150	5	650	45	45	August 31, 2018
D	M	A150	5	65	7500	15	April 10, 2018
D	M	A300	10	140	3420	10	April 3, 2018
E	S	A150	15	20	10525	85	January 3, 2018
B	S	A150	5	30	10500	40	June 3, 2018
B	S	A150	5	450	450	64	April 3, 2018
E	XS	A900	5	45	75	60	January 3, 2018
F	M	A900	15	95	655	175	January 3, 2018
D	XL	A300	5	15	21500	25	January 3, 2018
D	S	A500	10	450	65	25	May 3, 2018
A	M	A350	15	250	450	22	January 3, 2018
B	S	A150	15	45	8500	28	January 3, 2018
A	S	A300	5	550	650	128	January 3, 2018
C	M	A150	5	1500	855	190	January 3, 2018
B	M	A150	10	65	1750	41	January 3, 2018
A	L	A500	15	75	1700	24	January 3, 2018
B	S	A900	10	55	9800	37	May 29, 2018
B	M	A500	5	150	850	83	April 18, 2018

I was hoping to have a few Calculated Columns where:

• Column 1: shows common Models between A & B only and hides the uncommon
• Column 2: shows common Groups between A & B only and hides the uncommon
• Column 3: shows common Product Number between A & B only and hides the uncommon

Also, is there a way to concatenate those common items in a measure to be used as tooltip in a chart & to be used in a Card visual for example?

Can you please show me how to do this because I don't know what syntax to filter out uncommon items while retaining common ones.

Thank you so much!

[Anonymous · Accepted solution]

Below is the model column. Follow its pattern for the other two.

This definition will give you a TRUE/FALSE for every row, even if it's not a Vendor A,B row. If you only want to show this value for vendor A,B, surround it in

IF(Table1[Vendor] in {"A","B"}...

AB_CommonModel =

CONTAINS(FILTER(Table1, Table1[Vendor] = "A"), Table1[Model], Table1[Model])

&& CONTAINS(FILTER(Table1, Table1[Vendor] = "B"), Table1[Model], Table1[Model])