Forum Discussion
Select values from different tables based on a condition
Hi everyone, I am new in Power BI so I hope I can explain my issue so I could get some help from the group.
I've searched in many places without much success so far.
I have several tables containing financial information. I could link the tables via the journal numbers.
One Table contains all the GL journals with the total values. Each line contains the journal number, GL account, subledger and total amount.
GL table
| journal nr | gl account | subledger | value | date |
1 | 2000 | AP | -100 | 01.03.2022 |
| 1 | 6210 | AP | 100 | 01.03.2022 |
The other tables are the subledgers and contain the details of each journal. Depending of the subledger it will have the customer name (AR subledger), supplier's name (AP subledger). It happens that what in the GL is shown in one line as a total, in the subledger you can see the detail (several lines, several suppliers,... with the same journal number)
for example the below would be the AP subledger
| journal nr | GL ACC | supplier nr | supplier name | date | value |
| 1 | 2000 | AP1 | SUPPLIER1 | 01.03.2022 | 25 |
| 1 | 2000 | AP2 | SUPPLIER2 | 01.03.2022 | 50 |
| 1 | 2000 | AP3 | SUPPLIER3 | 01.03.2022 | 25 |
1 | 6210 | AP1 | SUPPLIER1 | 01.03.2022 | 25 |
| 1 | 6210 | AP2 | SUPPLIER2 | 01.03.2022 | 50 |
| 1 | 6210 | AP3 | SUPPLIER3 | 01.03.2022 | 25 |
Similarly there would be an AR table too, inventory, Fixed Assets....
I would like to have a visualization showing:
| journal nr | gl account | date | supplier/customer | value |
So,
1 the supplier/ customer column would change depending if in the GL table it says subledger (AP/ AR,.....) , if the transaction is a GL only transaction, it should have to be left blank.
2 The values. Depending if the journal is in a subledger or now it will change as well to allow the possibility of show the detail per customer or supplier.
I do not know which DAX transactions could help me in these case. Maybe my issue has a simpler solution.
I tried to simplify it, I still hope it's clear what my issue is and you could direct me to a solution.
Thanks in advance for your help.
M.
- Anonymous4 years ago
Thanks Ibendlin,
I ended up appending all my tables in a big one. (AP, AR, Fixed Assets, GL,...).
thanks.
M.
2 Replies
- lbendlinSuper User
This is something you want to prepare before DAX. In Power Query add a column to the Subledgers tables with the account type, ie "Customer" or "Supplier". Then append these tables. After that you can load the final tables into Power BI and wire them up in the data model.
- AnonymousNot applicable
Thanks Ibendlin,
I ended up appending all my tables in a big one. (AP, AR, Fixed Assets, GL,...).
thanks.
M.