Filtering by Dimension

[FuriousDawg33]

Hello,

I am having trouble filtering data from Business Central in Power BI. I am trying to filter by departments and I have been able to filter by departments for individual expenses, but cannot figure out how to filter total expenses. I have created relation ships through G_LEntries, DimensionSet Entries, and Financial Statements and it does not seem to work. I created a relationship between Dimension Value ID and Dimensions Set ID with the DimensionSetEntreies and G_LEntries tables. Then I linked G_LEntries wiht Fianicial Statmetns through the G_L_Account_Names. I saw an example with the Chart_of_Accounts but that isn't where Im pulling my data from. With that being said I still tried to use the Chart_of_Accounts but it did not work.Any help pointing me in the right directions would be  much apprieciated. Fianally, I do have a date table with the correct relation ship.

Thank you.

[v-yohua-msft · Accepted solution]

Hi, @FuriousDawg33 

If you're trying to aggregate total costs filtered by department, you might consider using a DAX measure to calculate this value. You can refer to the following DAX

Total Expenses by Department = CALCULATE(SUM('Financial Statements'[Amount]), 'DimensionSet Entries'[Department] = "YourDepartment")

If your model needs to be filtered across multiple tables, and the filters aren't propagating as expected, look at the filter direction in the relationship. Sometimes it helps to set up a relationship with two-way filtering, but use this feature with caution as it can lead to unexpected results in complex models. More information about two-way filtering:

Bidirectional cross-filtering in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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