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

[Berl21]

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!

[Greg_Deckler · Accepted solution]

@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]