When building Power BI data models for a use case involving multiple companies, each with distinct cost centers and accounts, a common structure involves linking dimension tables (e.g., Cost Centers, Accounts, Companies) to a shared fact table like General Ledger Transactions.



This generally works fine when working with outputs (actuals from the general ledger), as the active combinations are naturally connected through transactional data. However, when you work with inputs, challenges arise when trying to filter dimensions, such as Cost Centers and Accounts by Company. For instance, with budgeting and forecasting, users may need to display accounts that lack historical transactions. The solution requires a data model design that enforces company-specific filtering while maintaining active relationships between dimensions and fact tables.

This could be done in a number of ways, and in this chapter we will present one approach to solve this.

Make sure you have inactive relationships between your company table and the other dimension tables that needs to be filtered through the company dimension. In this case, those tables are DimCompany & DimCostCenter, and DimCompany & DimAccount. These are linked using the CostCenterCompanyKey-field, and the AccountCompanyKey-field in each table. 



Using DAX, you can now create a temporary table to generate values for valid combinations of keys.





The first part of the measure creates the temporary table. Using this, we summarize the number of current filtered combinations to be used in slicers and to push/limit rows into the planning form. Note, that you must use SUMX, and not SUM to be able to perform this calculation. 

You can use this measure on the related slicers to filter out valid combinaitons of members each dimension should have visible.

Using measures to filter visuals is generally not recommended because measures recalculate dynamically for each visual based on the filter context. This process can be resource-intensive, potentially slowing down performance, especially in complex scenarios.

While the impact on performance might be minimal in simpler contexts, such as using a slicer, it becomes significant in visuals like Aimplan Planning and Reporting Visuals. These visuals often involve a more intricate setup of fields and measures, resulting in a resource-heavy solution when filtering based on a measure. The added complexity of the filter context further amplifies the performance challenges.

To get around this issue, calculated groups could be far more efficient and maintains better report responsiveness.

In the next section, Filter Rows Using Calculation Groups, we will address how to use Calculation Groups to limit what rows should be displayed in an Aimplan Visual, without using the measure as an actual filter on the visual.