calculation issue
1 Topic
How to Combine Multiple Goal Achievement Measures with Different Visual-Level Filters
Hi everyone, I’m working on a Perfect Store / Perfect Score concept in Power BI, where each retail outlet is evaluated based on several criteria from our Dynamics 365 data — such as Distribution, Shelf Placement, and Secondary Placement. Each of these topics is handled on a separate Power BI report page, with its own data source tables and logic: Distribution: based on data from the Distributions table Shelf Placement: based on data from Business Activities and Promotions Secondary Placement: based on Orders and Promotions Each topic has its own measure for goal achievement (target vs. actual). However, these measures use different Visual-Level Filters that are applied in the Filter pane on each individual Report Page. The challenge: Now, I need to calculate one national goal achievement measure that combines all three topics. The problem is that each topic’s measure depends on its own set of filters (currently set at the visual or page level). When I try to combine them using DAX (REMOVEFILTERS, CALCULATE, etc.), the results are incorrect because the filter context from the individual report pages is not preserved. I know I could move all those filters into the DAX measures (e.g., by hardcoding the list of relevant Promotions or Business Units for each topic), but this would mean adding and maintaining dozens of filters directly inside the measures. Given the complexity and the number of filters, this approach would be very time-consuming and hard to maintain in the future. My goal: I’m looking for the most efficient and low-maintenance approach to achieve the following: Combine the existing topic-specific goal achievement measures (Distribution, Shelf Placement, Secondary Placement) into a single national measure. Ensure that the logic for filtering the relevant data (which currently exists as Visual-Level Filters) is either automatically inherited or centrally maintained. Avoid having to manually rewrite or hardcode all filter conditions in each DAX measure. Question: Has anyone faced a similar situation where measures are calculated under different visual-level filters, but need to be combined into one aggregated measure? How did you handle the filter logic in a way that minimizes maintenance effort in the long term? I would really appreciate your help 🙂 Best regards, JanSolved1.2KViews0likes6Comments