How to sum a rate from a dimension table only for the keys present in my fact table?
Hi Power BI Community, I have a classic star schema model with a fact table and a dimension table. My dimension table includes a numeric rate/price field (DiscountPlanEffectivePrice) and a key called DateDiscountPlanMeterId. This key is also present in my fact table, and it's used for joining the two tables. In my matrix visual, I’m grouping by various topology fields (like region, offer, pricing model, etc.) from my fact table, and I want to display the sum of the unique DiscountPlanEffectivePrice values from the dimension table, but only for the keys that actually exist in the current filter context of my fact table. My goal is to: Avoid summing all rates from the dimension, and Avoid duplicating the same rate for every related fact row. I’ve tried DAX measures like: Sum of Effective Discount Prices = SUMX ( VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]), CALCULATE( SELECTEDVALUE(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice]) ) ) and also attempted using SUMMARIZE, but I’m either getting blank results or incorrect totals. The relationship between the tables is active and based on DateDiscountPlanMeterId. How can I correctly sum only the unique rates from the dimension for keys that are present in my fact table’s current context? Is there a better DAX pattern, or am I missing something in the way context or relationships should be handled for this scenario? Any suggestions or guidance would be greatly appreciated! Thanks in advance! Jacek
