Forum Discussion
trdoan
Helper III
Helper IIICalculated column to show duplicate/common items and hide uncommon items
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!
- 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 inIF(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
- 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 inIF(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])