Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Comparing Multiple Columns in Rows between Two Queries

I am wondering how to see if there is a row in one query that matches 3 or 4 columns in a different query. For example, I have a job query and an invoice query. I want to know if someone created an invoice for every job. So if a job row has a match on 3 columns in a row of the invoice query it will count it, but otherwise it will not count it. It would also need to ignore duplicates if two rows matched the original row

 

 

A simple example of the two tables:

tables example.JPG

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous - I would do the following:

1. Create a new Calculated Column on each table, which concatenates the 3 columns that need to be joined.

2. Create a Relationship between the two tables on this column.

3. Create a Measure like:

First Invoice = MIN('Invoice Query'[Invoice Number])

Hope this helps,

Nathan

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may simply try CONTAINS to add a calculated column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-chuncz-msft 

 

how woud you create the calculated column for each table? Please can you show me an example?

 

Thanks

Anonymous
Not applicable

@Anonymous - I would do the following:

1. Create a new Calculated Column on each table, which concatenates the 3 columns that need to be joined.

2. Create a Relationship between the two tables on this column.

3. Create a Measure like:

First Invoice = MIN('Invoice Query'[Invoice Number])

Hope this helps,

Nathan

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.