Forum Discussion

LoganFFS's avatar
LoganFFS
Frequent Visitor
1 year ago
Solved

Calculated Column

Hi there, I am trying to make a calculated column that simply pulls a value from another column if the preceeding row has a certain value. in a different column. I have an index column named "Row #" ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I tried to use OFFSET DAX function in the calculated column formula.

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    value(price) calculated column = 
VAR _nextrow =
    MAXX (
        OFFSET ( 1, SUMMARIZE ( data, data[row#] ), ORDERBY ( data[row#], ASC ) ),
        data[row#]
    )
VAR _nextaction =
    MAXX ( FILTER ( data, data[row#] = _nextrow ), data[action] )
VAR _condition = _nextaction = "sell"
    && data[action] = "buy"
RETURN
    IF ( _condition, data[price] )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
1 year ago

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to use OFFSET DAX function in the calculated column formula.

 

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

value(price) calculated column = 
VAR _nextrow =
    MAXX (
        OFFSET ( 1, SUMMARIZE ( data, data[row#] ), ORDERBY ( data[row#], ASC ) ),
        data[row#]
    )
VAR _nextaction =
    MAXX ( FILTER ( data, data[row#] = _nextrow ), data[action] )
VAR _condition = _nextaction = "sell"
    && data[action] = "buy"
RETURN
    IF ( _condition, data[price] )