cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trdoan Member
Member

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

Hi everyone,

 

Here is my table called "Data":

 

VendorSize GroupModelProduct NumberQuantityCostTATPosting Date
ASA150515045067July 7, 2018
AMA2005250150075June 22, 2018
AMA150525850085July 9, 2018
CLA200103501250125March 5, 2018
CXLA5005150650045February 20, 2018
AMA9001038547540January 29, 2018
AMA15056504545August 31, 2018
DMA150565750015April 10, 2018
DMA30010140342010April 3, 2018
ESA15015201052585January 3, 2018
BSA1505301050040June 3, 2018
BSA150545045064April 3, 2018
EXSA9005457560January 3, 2018
FMA9001595655175January 3, 2018
DXLA3005152150025January 3, 2018
DSA500104506525May 3, 2018
AMA3501525045022January 3, 2018
BSA1501545850028January 3, 2018
ASA3005550650128January 3, 2018
CMA15051500855190January 3, 2018
BMA1501065175041January 3, 2018
ALA5001575170024January 3, 2018
BSA9001055980037May 29, 2018
BMA500515085083April 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!

1 ACCEPTED SOLUTION

Accepted Solutions
durschel Frequent Visitor
Frequent Visitor

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

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])
 
 
1 REPLY 1
durschel Frequent Visitor
Frequent Visitor

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

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])