Matching based on conditions

[OECvargoj]

Hello,

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.

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

Does anyone have a solution for this?

[Ashish_Mathur]

Hi,

What exact results are you expecting?

[Zubair_Muhammad]

@OECvargoj

Try this column for more than 1 match

Column =
CONCATENATEX (
    FILTER (
        ALL ( 'Table1' ),
        Table1[Part Number] = EARLIER ( Table1[Part Number] )
            && Table1[Stock Status] <> EARLIER ( Table1[Stock Status] )
    ),
    Table1[Company],
    ", "
)

[Zubair_Muhammad]

@OECvargoj

[BraneyBI]

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

[OECvargoj]

Thank you @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?