Forum Discussion
Relation based on two columns
Hello!
I need help creating a relationship between two tables. I have two tables regarding ledger entries; one table with the details for each entry, the other table with the details for each entry-batch.
For example, in table 1 there are 3 columns: description, entry number and amount.
[Purchase of goods] [entry 1] [€100]
[Purchase of service] [entry 2] [€350]
[Purchase of goods] [entry 3] [€20]
And in table 2 there are 4 columns: date, booking type, first entry number, and last entry number (of the batch)
[Date] [Purchases] [entry 1] [entry 3]
Now, I want to show all purchases that are done on [Date]. So, I need table 2 to show the individual entries and their descriptions, instead of just the first and/or last one. Basically, I need a relationship based on 'first entry < entry# > last entry'.
Does anyone know how to do this, with a DAX or PowerQuery for example?
Thanks!
Hi Robin9700 ,
I created another new sample pbix file(see attachment) base on the data of your provided tables, please check whether that is what you want.
Best Regards
7 Replies
If these columns are integers-> [entry 1] [entry 3] , then a fairly elegant Power Query solution is available.
How do you link the batch between the 2 tables though?
Hi Robin9700 ,
Could you please provide some sample data(exclude sensitive data) in table 1 and table2 and your expected result with examples just as show in below tables? Thank you.
1. Sample data
table 1
description entry number amount Purchase of goods entry 1 100 Purchase of service entry 2 350 Purchase of goods entry 3 20 table 2
date booking type entry 1 entry 3 2021/5/12 purchase 20 30 2. Expected result?
date booking type entry number description value 2021/5/12 purchase entry 1 Purchase of goods 20 2021/5/12 purchase entry 3 30 Best Regards
Hi, thanks for your reply!
The result I want to show from the two tables, is:
Entry# - date - description - amount(€)
Table 1 shows the description and the amount. Table 2 contains the date and entrynumbers, however the entrynumbers are shown as "entry1 up to entry4", with both the first and last entry# of the 'batch' in different columns.
To see the date for 1 specific entry number, for example entry2, I need to use something like a VLookup would do in Excel, I think. However, I am not able to establish a relationship between the 2 tables at this moment. Do you have any suggestions?
Hi Robin9700 ,
Sorry for delay. I created a sample pbix file(see attachment), please check whether that is what you want.
If the above one is not what you want, please provide some sample data in your tables and your expected result with specific examples and involved logic. Thank you.
Best Regards
Anonymous Thanks again for your reply!
Table 2 is indeed exactly what I need.
Table 1 is like the one I already have.
I see I am not allowed to upload files and/or images, so I will try to explain this again.
Table 1 looks like this:
Description Entry number Amount Purchase of goods 1 €100 Purchase of goods 2 €350 Purchase of goods 3 €20 Sales 4 €125 Sales 5 €250 Purchase of goods 6 €50 Purchase of goods 7 €100 Purchase of goods 8 €10 And the other table I have (which is not in your file) looks like this:
Date Description First entry number Last entry number May 12, 2021 Purchase of goods 1 3 May 17, 2021 Sales 4 5 May 20, 2021 Purchase of goods 6 8 And what I need right now, is a way to connect or merge those 2, to eventually create visuals with the following data (like table 2 in your file):
Date Description Entry number Amount May 12, 2021 Purchase of goods 2 €350 May 17, 2021 Sales 4 €125 May 20, 2021 Purchase of goods 6 €50 May 12, 2021 Purchase of goods 1 €20 So basically, what I need, is table 1 & 2 to form table 3 together. Can you help me with that?
Hi Robin9700 ,
I created another new sample pbix file(see attachment) base on the data of your provided tables, please check whether that is what you want.
Best Regards
Thank you so much! I was able to recreate the 'Entries' column and expand it, and now my data does what it should do 🙂
Have a good day!
