Forum Discussion

FIJT's avatar
FIJT
Frequent Visitor
4 years ago
Solved

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:   Where Optimal Energy is calculated with:   Optimal Energy = VAR __Energy = ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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

  • FIJT's avatar
    FIJT
    Frequent Visitor
    4 years ago

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

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        FIJT
        Frequent Visitor
        4 years ago

        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 ğŸ™‚