Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FIJT
Frequent Visitor

Calculate column based on filtered values

Hello everybody,

 

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

I have created a following column:

FIJT_0-1632468731565.png

 

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.
FIJT_1-1632469173834.png

 

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 🙂
1 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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

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

@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

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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.