Nested Context using ALLEXCEPT and ALLSELECTED

0
votes

I am facing a problem right now that I can’t find the answer to in any online research.

I have a calculated measure as follows…

top_selected_tier:=CALCULATE(
    min(Table1[waterfall_tier]),
    ALLSELECTED(Table1)
)

…that shows the correct value in the measure pane, after slicing the [waterfall_tier] field (using either slicers or filter dropdowns).

However, when I reference that measure in a calculated column…

[test_top_tier]=[top_selected_tier]

… I get a different value. It essentially returns the lowest value in the table, irrespective of which set of filters/slicers are applied.

What I need to test for is whether the field [waterfall_tier] at the row level matches the currently lowest value for [top_selected_tier] that is selected via slicers as reported in the measure pane.

Similarly, in another calculated column I am trying to do a sum of all “filled” requests in the table for a given date, and I don’t know how to construct the formula to only sum the selected values (via slicer/filter dropdown). Instead this formula sums all the values for that day, irrespective of the set of filters/slicers applied.

[all_filled]=CALCULATE(
    sum([requests]),
        FILTER(ALLEXCEPT(Table1,Table1[request type],Table1[date]),
            [request type]="filled"
        )
)

In effect I need the formula to return an ALLEXCEPT on the ALLSELECTED subset of table… if that makes sense?

2
powerbipowerpivotdaxexcel-2016

2 Answers

0
votes

Please note the features of the CALCULATE:

  1. New filter context is added.
  2. The function transform existing row contexts into an equivalent filter context.
  3. The preceding simultaneous occurrence.
0
votes

Calculated columns are not dynamic, that's why you are getting a fixed value. (see this article)

One important concept that you need to remember about calculated columns is that they are computed during the database processing and then stored in the model.

This means that the column value is evaluated only once, in the beginning, when there are no filters yet, and therefore the "ALLSELECTED" dataset is the whole table.

In PowerBI (sadly) you cannot create a dynamic column.