Find matching rows in one table

[kellysolutions]

I am not sure if this problem is better addressed in DAX or Power Query.

I suspect DAX.

I have a table with 3 key columns.

Customer ID
Job Number
PO Number.

Job No is Used by Customer1 and PO Number is used buy Customer2.

Customer1 buys from Customer2 and Customer2 uses the Job Numner of Customer1 as the PO Number.

I want to retrieve all of the data in any row that does not belong to Customer1, where the Job Number matches the PO Number.

select * from table where Customer ID <> Customer 1 and Job Number = PO Number.

Thank you.

[EnterpriseDNA]

Hi @kellysolutions ,

Given your requirements, using DAX to create a calculated table in Power BI would be an efficient way to address this problem. Here’s how you can do it:

1. Create a Calculated Table: This will filter out the rows where the Customer ID is not Customer1 and the Job Number matches the PO Number.

Here’s the DAX formula to create the calculated table:

DAXCopy codeFilteredTable = FILTER(    YourTableName,    YourTableName[Customer ID] <> "Customer1" &&    YourTableName[Job Number] = YourTableName[PO Number])

1. Steps to Implement in Power BI:

• Open your Power BI Desktop.
• Click on Modeling in the top menu.
• Click on New Table.
• Enter the above DAX formula, replacing YourTableName with the actual name of your table.

This approach will create a new table in your data model with only the rows that meet the criteria you specified. You can then use this table for your reporting needs.

For more advanced or specific scenarios, you can explore using DAX functions such as CALCULATETABLE and more complex filtering logic as needed. If you have any further questions or need more customized solutions, you can run your queries at the Enterprise DNA Data Mentor platform.

Cheers,

Enterprise DNA Support Team

[kellysolutions]

Thank you for your reply, but I have made an error in how I described the issue.

A table might have been better.

Column 1	Column 2	Column 3	Column 4
Customer	Job Number	PO Number	Note
Customer 1	1
Customer 2	2
Customer 3	3	1	This row should be returned because customer is not Customer 1 and PO Number matches Customer 1 Job Number
Customer 1	4
Customer 2	5
Customer 3	6
Customer 1	7
Customer 2	8	4	This row should be returned because customer is not Customer 1 and PO Number matches Customer 1 Job Number
Customer 3	9

[Roger]

Paul

a simple solution is to import the table, reference it and do a merge between the 2 tables
job number with the PO number ( screen shot ).
The result in the other screen shot

this requires no DAX , if you are looking for another solution ( DAX ) please let me know

kind regards

Roger

[Roger]

[kellysolutions]

Thank you Roger.

What would happen if the realtionship between job number and po number was one to many

[Roger]

Paul

it still works

I changed the data from your sample set and the merged table works fine
po and job number.xlsx (10.1 KB)
JOB NUMBER and PO NUMBER.pbix (44.7 KB)

attached the pbix and you altered data set

kind regards

Roger

[kellysolutions]

Thank you for your help roger