I have a suspected bug question and want to ask

[xifeng]

The background is: when there are slicers of the same table category and subcategory at the same time in the report environment, they will trigger the Auto-Exist mechanism. Then use the matrix visual object in the evaluation environment to accumulate the values ​​of the subcategories, where the row labels are the subcategories.

The measures used are:

Cumulative-WhyError=
    VAR CurValue = [SumOfValue]
    RETURN
    CALCULATE(
        [SumOfValue],
        FILTER(
            ALLSELECTED('Table'[SubCategory]),
            [SumOfValue]>=CurValue
        )
    )

The results of the above measure did not meet expectations. After modifying the above measure, we get:

Cumulative-Correct =
VAR CurValue = [SumOfValue]
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ('Table'[SubCategory] ),
            [SumOfValue] >= CurValue
         ),
        [SumOfValue]
    )

The result of this modified measure is completely correct. So what is the difference between these two measures, and what is the reason for the incorrect result of the first measure?

[v-kkf-msft]

Hi @xifeng ,

Because the context in these two formulas are different.

For the matrix, we use the Performance analyzer to find its query.

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"A",
      "B"}, 'Table'[Category])

  VAR __DS0FilterTable2 = 
    TREATAS({"A1",
      "A2",
      "B1",
      "B2"}, 'Table'[SubCategory])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Table'[SubCategory], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "SumOfValue", 'Table'[SumOfValue],
      "Cumulative_Correct", 'Table'[Cumulative-Correct],
      "Cumulative_WhyError", 'Table'[Cumulative-WhyError],
      "Cumulative_WhyCorrect", 'Table'[Cumulative-WhyCorrect]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, [SumOfValue], 0, 'Table'[SubCategory], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, [SumOfValue] DESC, 'Table'[SubCategory]

1. For the measure [Cumulative-WhyError], the filter context is:

           __DS0FilterTable,
           __DS0FilterTable2,

which allows this measure to group by Category and perform cumulative calculations on SubCategory. So you need to remove the filter context on the Category column, e.g. ALLSELECTED ( 'Table' ).

Cumulative-WhyError = 
var CurValue = [SumOfValue]
return 
CALCULATE (
    [SumOfValue],
    FILTER(
        ALLSELECTED ( 'Table' ),
        [SumOfValue] >= CurValue
    )
)

2. For the measure [Cumulative-WhyCorrect], it differs from [Cumulative-WhyError]. Because the measure [SumOfValue] is referenced in [Cumulative-WhyError]. Using the measure [SumOfValue] inside another Measure will apply a context transition when using filtering functions. For more information, please refer to: DAX Context Transition: Why it can be handy to use a [Measure] inside a Measure 

3. For the measure [Cumulative-Correct], using Iterator functions within a measure will change the Filter Context to a Row Context of the Table defined in the DAX expression. For more information on how to determine the context of iterators, you can refer to the following thread that I have provided detailed description.

Solved: Context in Iterator functions: dummy example 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xifeng]

Hi,@v-kkf-msft,

Thank you for your reply, but I do not agree with your second point.

---

@v-kkf-msft wrote:

2. For the measure [Cumulative-WhyCorrect], it differs from [Cumulative-WhyError]. Because the measure [SumOfValue] is referenced in [Cumulative-WhyError]. Using the measure [SumOfValue] inside another Measure will apply a context transition when using filtering functions. 

---

Because [Cumulative-WhyCorrect] and [Cumulative-WhyError] the value of the CurValue variable in the two measurement values has been fixed after the first calculation. The CurValue variable will not continue to be calculated during the calculation of the FILTER function, so It will not trigger a row context transition.

Finally, from the results of [Cumulative-WhyCorrect], it can be known that whether to remove the category filter is not the key, because the value of the measure value is 11, which already contains the results of the two categories

So I think this problem should be a bug

[lbendlin]

What happens when you do this?

Cumulative-WhyError=
    VAR CurValue = [SumOfValue]
    RETURN
    CALCULATE(
        [SumOfValue],
        ALLSELECTED('Table'[SubCategory]),
        [SumOfValue]>=CurValue
    )