cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Matching based on conditions

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?

5 REPLIES 5
Super User

Hi,

What exact results are you expecting?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

@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],
", "
)```

Regards
Zubair

Community Champion

@OECvargoj

Regards
Zubair

Kudo Commander

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

Frequent Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors