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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Join tables with two keys

Hi Guys,

 

I hope you can help me with the problem I'm facing.

I have two tables, let's say Contract and Revenue.

 

Revenue

| Customernr. | Subcategory | Subcategory (Groups) | Revenue in € |

| 1                   | Blue Paint     | Paint                            | €40                |

| 1                   | Red Paint      | Paint                            | €50               |

| 2                   | Planks           | Wood                          | €170              |

 

 

Contract

| Customernr. | Category | Amount in € |

| 1                   | Paint       | €100             |

| 1                   | Metal      | €250             |

| 1                   | Wood     | €200             |

| 2                   | Paint       | €500             |

| 2                   | Metal      | €250             |

| 2                   | Wood     | €200             |

 

In my Report view I want a matrix table with the customer number and category with the amount in € and the SUM of revenue in € behind it. That way I can check if the customer buys under the contracted amount.

 

The problem I am facing right now is that I cant merge these two tables because I use Groups. Groups aren't visible in the query editor. So I have to solve this problem outside the query editor.

What is the best way to sum the revenue in € based on the customer number and subcategory (groups)?

 

This is what I want:

| Customernr. | Category | Amount in € | Revenue in € |

| 1                   | Paint       | €100             | €90

| 1                   | Metal      | €250             | €0

| 1                   | Wood     | €200             | €0

| 2                   | Paint       | €500             | €0

| 2                   | Metal      | €250             | €0

| 2                   | Wood     | €200             | €170

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I figured it out with the replies on this post.

 

I could not create a composite key in the query editor, because I have groups.

 

So I created a new column in both tables that uses the two columns and merges them as one. This is my key.

Now I could create a relationship between the two tables.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I figured it out with the replies on this post.

 

I could not create a composite key in the query editor, because I have groups.

 

So I created a new column in both tables that uses the two columns and merges them as one. This is my key.

Now I could create a relationship between the two tables.

IanCockcroft
Post Patron
Post Patron

Hi Tim, can you not create a compcite key in the query editor uing the two fields and then join the tables on the composite key?

 

something like this

Revenue    
CustomernrSubcategorySubcategory (Groups)Revenue in Ecomposite key
     
1Blue PaintPaint€ 401 Paint
1Red PaintPaint€ 501 Paint
2PlanksWood€ 1702 Wood
     
Contract    
CustomernrCategoryAmount in eur composite key 
1Paint€ 1001 Paint 
1Metal€ 2501 Metal 
1Wood€ 2001 Wood 
2Paint€ 5002 Paint 
2Metal€ 2502 Metal 
2Wood€ 2002 Wood 

 

Anonymous
Not applicable

@Anonymous  Please create a column by merging Customernr and Subcategory (Groups) in Revenue table.

Similarly create one column by merging Customernr and category in contract table and create a relationship between two tables

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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