Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 nrgl accountsubledgervaluedate

1

2000AP-10001.03.2022
16210AP10001.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 nrGL ACCsupplier nrsupplier namedatevalue
1

2000

AP1

SUPPLIER101.03.202225
12000AP2SUPPLIER201.03.202250
12000AP3SUPPLIER301.03.202225

1

6210AP1SUPPLIER101.03.202225
16210AP2SUPPLIER201.03.202250
16210AP3SUPPLIER301.03.202225

 

Similarly there would be an AR table too, inventory, Fixed Assets....

 

I would like to have a visualization showing:

journal nrgl accountdatesupplier/customervalue

 

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.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks Ibendlin,

    I ended up appending all my tables in a big one.  (AP, AR, Fixed Assets, GL,...). 

    thanks.

    M.

2 Replies

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks Ibendlin,

      I ended up appending all my tables in a big one.  (AP, AR, Fixed Assets, GL,...). 

      thanks.

      M.