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
Create a slicer with a Mesure
Hi everyone ! I'd like to create a slicer segment with a “rate” (measure) that lets you choose a rate range. For example: Here I can select rate from 0% to 73% but it can be 5% to 13% also. I know it's easily possible in Tableau but Power BI it seems no. Can you give me a tips in DAX to do that please ? You need to know, I don't manage the dataset (power query) because it managing by IT department. Thank you a lot and have a good day, LD
Evolution rate only for common months
Hello guys, I'm trying to display an evolution rate which compares datas over 2 years. BUT, I only want common months to be part of the calculation. (In 2022, I have datas for 6 months, and in 20221 I have datas for 5 months). I just want my measure to calculate the rate between the 5 common months. Here is an extract of my data. And here is my DAX measure : Rate N /N-1 = divide ( sum('file'[Data N])-sum('file'[Data N-1]),sum('file'[Data N-1]),"NA") Of course, I want to be able do display my charts for each year, using a slicer for years. Can someone give me the solution please ? I'm sure it's not that difficult ! Thans a lot !
