Forum Discussion
Overriding filter context and duplicate measure value
Hi everyone, the title probably does not accurately describe the problem but I hope the following example can. I have a measure [M_test sum] that sums the values based on the Attribute column using an inactive relationship (may/may not be relevant here). Attribute 2 always returns blank because the fact table only has attributes 1, 3, 4, 5. I would like this measure use Attribute 1 as the filter to sum Attibute 2, essentially replicating the sum of Attribute 1 (see Proposed below). The reason why this is needed is because the [Sum of Value] column will have different values for Attribute 1 and 2 which is sourced from a different table. [Sum of Value] and [M_test sum] will be compared against each other.
Current:
Proposed:
EDIT: The found a costly workaround which is to create a calculated column in the fact table that converts all Attribute 1 to 2 and create a measure (and a new inactive relationship) that sums this new column. Finally, sum the 2 measures to get desired results. Still prefer a solution that doesn't introduce calculated columns in the fact table.
2 Replies
- amitchandak
Super User
TRADER083 , Try a measure like , Asuuming you have a meausre [M Test Sum]
if(not(isblank([M Test Sum]) ), [M Test Sum],
CALCULATE([M Test Sum],OFFSET(-1, ALL('Table'[Attributr]),ORDERBY('Table'[Attributr]))) )- TRADER083
Helper II
Thank you. It looks promising but I couldn't get to work properly. It may be because Attribute 2 only exists in the dimension table and not in the fact table. The measure will offset (-1) the values from Attibute 1 to 3 instead from 1 to 2.