Matching based on conditions

Question

Hello,&nbsp;I'm trying to display matching part numbers based on them having an opposite Stock Status. Using the example data below, I need a way to match Company A with Company B based on them both having Part Number 123ABC in their inventory while at the same time having opposite Stock Status.&nbsp;CompanyPart NumberStock StatusA123ABCStockB123ABCNo StockC456ABCNo StockD456ABCStockE789XYZStockF789XYZNo StockG741QWEStockH741QWENo Stock&nbsp;Does anyone have a solution for this?

braneybi · Answer

Assume the table is called "Table1", add a calculated COLUMN which will show the reciprocal company.

=CALCULATE( VALUES('Table1'[Company]),FILTER(ALL('Table1'),Table1[Part Number] = EARLIER(Table1[Part Number])&&Table1[Stock Status]<>EARLIER(Table1[Stock Status])))

zubair_muhammad · Answer

OECvargoj&nbsp;

ashish_mathur · Answer

Hi,&nbsp;What exact results are you expecting?

oecvargoj · Answer

Thank you&nbsp;BraneyBI! The calculated column is working however, when I add more than 2 companies to my dataset, I get an error message. Is there any way to modify the calculated column to account for more companies and possibly more than one match?

zubair_muhammad · Answer

OECvargoj&nbsp;Try this column for more than 1 match&nbsp;Column =
CONCATENATEX (
    FILTER (
        ALL ( 'Table1' ),
        Table1[Part Number] = EARLIER ( Table1[Part Number] )
            &amp;&amp; Table1[Stock Status] &lt;&gt; EARLIER ( Table1[Stock Status] )
    ),
    Table1[Company],
    ", "
)&nbsp;

Forum Discussion

Matching based on conditions

5 Replies

Related content

Conditional formatting based on rules

Conditional formatting based on multiple conditions

Conditional Formatting Based on Measure

Column Formula : Based on condition

delete rows based on conditions

Company	Part Number	Stock Status
A	123ABC	Stock
B	123ABC	No Stock
C	456ABC	No Stock
D	456ABC	Stock
E	789XYZ	Stock
F	789XYZ	No Stock
G	741QWE	Stock
H	741QWE	No Stock