How to make a connection between two transaction tables?

[Anonymous]

I have a problem with the connections in data model. I need to get information from two transaction tables which are from Microsoft Navision. The first table is “Item Ledger Entry” and it has the following columns: “Item No_”, “Entry Type”, “Document No_”. The second is “Value Entry” with  columns: “Document No_”, “Item Ledger Entry Type”, “Cost Amount (Actual)”. First We go to the table “Item Ledger Entry” and there we filter by “Entry Type” = 6 and “Item No_” and we get the “Document No_”. Then We have to filter the second table “Value Entry” by this “Document No_” and we filter by “Item Ledger Entry Type” = 5 and this way we have to get the information about SUM(Cost Amount (Actual)). How I construct the architecture of data model or is there any possible way to do this with DAX formula? If so could you explain how to proceed with the DAX formula here?

[amitchandak]

@Anonymous , is Document No_ is unique in "Item Ledger Entry", if so you can join the two tables on that can filter both parameters using a slicer or can create a measure with filters.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[GilbertQ]

Hi there

I would suggest importing this data in Power Query

Then apply your exact logic you have explained above in Power Query. This will allow you to merge the 2 tables together into a single table.

Once you have your single table you can then import it into your dataset?