Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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