Calculate column based on filtered values

[FIJT]

Hello everybody,

I'd like to ask you for help with a calculated column.

I have created a following column:

Where Optimal Energy is calculated with:

Optimal Energy = VAR __Energy = [Energy Cost]

VAR __MinPrevInvestment = MINX(FILTER('Table',[Energy Cost]<__Energy),[Investment])

RETURN

IF([Investment]<__MinPrevInvestment, [Energy Cost], BLANK())

When I use a slicer filter in the report for energy cost, the column does not update the calculation.

I've tried to use ALLSELECTED in the formula, but can't seem to make it work.

Is there a way to update the calculated column based on filtered values?

Thanks for reading 🙂

[amitchandak]

@FIJT , A calculated column can not get updated with a slicer value. Slicer values can only be used measures

[FIJT]

@amitchandak Is there a way to make the calculated column as a measure?

[amitchandak · Accepted solution]

@FIJT , Measure display depend on visual group bys

like

Optimal Energy = VAR __Energy = max(Table[Energy Cost])
VAR __MinPrevInvestment = MINX(FILTER(allselected('Table'),[Energy Cost]<__Energy),[Investment])

RETURN
IF(max([Investment])<__MinPrevInvestment, sum([Energy Cost]), BLANK())

or force a row context

RETURN
sumx(Values(Table[ID]), IF(max([Investment])<__MinPrevInvestment, sum([Energy Cost]), BLANK()) )

A row context of id is pushed in last return

[FIJT]

Thank you so much!

I used this to create multiple measures (don't know if it was necessary) ->

Measure 1 = MAX(Table[Investment])

Measure 2 = MAX(Table[Energy Cost])

Measure 3 =MINX(FILTER(allselected('Table'),[Energy Cost]<[Measure 2])),[Investment])

Measure 4= IF([Measure 1]<[Measure 3],[Measure 2],IF([Measure 3]<1,[Measure 2],BLANK()))

Measure 4 gives me the correct value and reacts to filters applied on the page. (Had to add the last IF, because it would skip the 1st value for some reason)

Thanks again @amitchandak 🙂