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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Berl21
Helper III
Helper III

Joining two tables with two fields instead of one (double segmentation)

Hi,

I am relatively new at PBI and I have an issue with datamodeling for a client.

We have two tables which are built in this way:

Expense Table Revenue Table
Account Nbr Account Nbr
Invoice Nbr Booking Nbr
Type of costs Type of action
Costs Revenue


In the first table, accounting is keeping tracked of expenses, in the second one it is keeping tracked of revenue. The goal is for us to deliver a report with the difference between costs and revenue segmented by account number but also the type of costs, which has the same naming convention as the type of action. I should have such a result:

Type of action 1
Revenue - Costs
Account Nbr. 1
 
Account Nbr. 2
 
  
Type of action 2
Revenue - Costs
Account Nbr. 1
 
Account Nbr. 2
 

If I do only one join and try to pull the other field in for the segmentation, it doesn't work.

How can I join a table twice with itself, and I either get the segmentation by type of action or the one by Account Nbr. by joining the tables on these fields. What is the best practice in such cases?

Thanks!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Berl21 Well, you could create a column in each table that concatenates the values together and relate them that way, like creating a column in each table:

 

Key Column = [Column1] & "_" & [Column2]



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

@Berl21 Well, you could create a column in each table that concatenates the values together and relate them that way, like creating a column in each table:

 

Key Column = [Column1] & "_" & [Column2]



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
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!