D365 FO: How To Filter Ledger Journal Entries Based on Cost Center Financial Dimension?

[I_NeedMorePower]

Greetings dears!

I have a Power BI report that reads data from D365 FO that shows the ledger journal entries, but I want to display the journal entries for a specific cost centers.

In the transaction line there is a field called "LedgerDimension".

I'm aware of the DimensionCombination entity but i'm not able to filter based on an individual financial dimension (Cost Center).

based on this link, I'm supposed to find a field in the DimensionCombination Entity that have the name of the defined financial dimensions.

So i'm supposed to have a field called "CostCenter", but unfortunately I was not able to find it.

How can I create a link between the GeneralJournalEntry.LedgerDimension and cost center dimension?

Any advice is appreciated !

Thanks in advance !

[I_NeedMorePower · Accepted solution]

Greetings,

I have found the solution to do that and I want to share it with anybody who will face the same requirement 🙂

First, I had to do a custom Data entity on the development environment of D365.

The root of the query for the data entity is the GeneralJournalAccountEntry Table.

This table contains all the ledger lines and it has a "LedgerDimension" field for the posted trans line.

Instead of doing a join for all the dimension tables, you will include a "method field" into the data entity, and you do the query to retrieve the dimension you need that is associated with transaction lines for the root table of your query in the code of the method that will be assigned for the method field (Create a method and write down your query to retrieve the dimension value you need based on the ledger dimension of the GeneralJournalAccountEntry table in your root query).

I found that this is faster then doing table joins on the query itself.

Give a name to the method field for example in my case, I will name it CostCenter (because I want to use this field to filter based on cost centers).

Now you can call this data entity on your Power BI and you will have your transactions with the dimension value displayed on it's new column created by you in the method field on the development environment 🙂

[v-jialluo-msft]

Hi  @I_NeedMorePower ,

According to your description, your requirement is to display data for a specific cost center, but you don't find a corresponding field to classify the data.
You can refer to the following links to model your data, and if that doesn't work, provide sample data and expected output.

Work with relationships and cardinality - Training | Microsoft Learn

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.