Forum Discussion
Filtering by Dimension
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.
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.
1 Reply
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.