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
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
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.