Forum Discussion

TRADER083's avatar
TRADER083
Icon for Helper II rankHelper II
2 years ago

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

  • 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's avatar
      TRADER083
      Icon for Helper II rankHelper 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.