Forum Discussion

GA2's avatar
GA2
Regular Visitor
3 years ago

Help with the Offset function

Trying to figure out the correct way to create the following column (in red) using the offset function with respect to equip_id partitions. The column "stat code" is a calculated column, dont know if that matters at all but any help you could provide is greatly appreciated. Thanks

 

 

EQUIP_STATUS_DATEEQUIP_IDCURRENT_FAC_IDStat CodeOFFSETEQUIP_STATUS_REASON_DESC
2/26/2023 14:3928168314Status 1Status 2.1 
2/25/2023 16:0128168314Status 2.1Status 1Miscellaneous
2/16/2023 19:2828168314Status 1Status 2.2 
2/15/2023 16:5628168314Status 2.2Status 3Scheduled Maint
2/14/2023 5:4728168314Status 3Status 1Mileage
2/11/2023 6:2128168314Status 1Status 2.1 
2/10/2023 15:1228168314Status 2.1 Miscellaneous
3/5/2023 14:0028168214Status 3Status 2.1Mileage
3/3/2023 17:5428168214Status 2.1Status 3Miscellaneous
3/3/2023 13:4628168214Status 3 Mileage
2/18/2023 9:3228168124Status 5Status 5Warranty
2/18/2023 9:3228168124Status 5Status 5Warranty
2/18/2023 9:3228168124Status 5Status 2.1Warranty
2/18/2023 9:3228168124Status 2.1 Running Repair
3/2/2023 21:1627756114Status 2.1Status 2.1Running Repair
3/2/2023 21:1627756114Status 2.1Status 1Running Repair
2/28/2023 18:5727756114Status 1  
Need Help

4 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello GA2,

     

    Could you please try: 

    Offset Column = 
VAR CurrentEquipID = [EQUIP_ID]
VAR CurrentRowIndex = ROW()
VAR PrevRowIndex = CALCULATE(
    MAX('Table'[Row Number]),
    FILTER('Table',
        'Table'[EQUIP_ID] = CurrentEquipID &&
        'Table'[Row Number] < CurrentRowIndex &&
        'Table'[Stat Code] <> BLANK()
    )
)
RETURN
IF(ISBLANK(PrevRowIndex),
    BLANK(),
    'Table'[Stat Code] & " -> " & OFFSET('Table'[Stat Code], PrevRowIndex - CurrentRowIndex, BLANK())
)
    • GA2's avatar
      GA2
      Regular Visitor
      3 years ago

      Thank you Sahir, am i supposed to create an index first for this calculated column? I do not get the [row number] sections and they give me an error

       

      This measurement worked however, when i'm trying to do a post if statement on the data it does not recongnize the data from the measurement. Is there a way to write this exact dax code as a calculated column?

       

      Offset measure = CALCULATE(max('TA_EQUIP_STATUS_HISTORY'[Stat Code]), OFFSET(-1, ALLSELECTED('TA_EQUIP_STATUS_HISTORY'[EQUIP_ID],'TA_EQUIP_STATUS_HISTORY'[CREATED_DATE],'TA_EQUIP_STATUS_HISTORY'[EQUIP_STATUS_CODE],'TA_EQUIP_STATUS_HISTORY'[Stat Code],'TA_EQUIP_STATUS_HISTORY'[EQUIP_STATUS_REASON_DESC]), ORDERBY('TA_EQUIP_STATUS_HISTORY'[CREATED_DATE]),,PARTITIONBY('TA_EQUIP_STATUS_HISTORY'[EQUIP_ID])))
       
      Thank you again so much for your help
  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    GA2 , Try like 

    Offset meausre = CALCULATE(max(Data1[Stat Code]), OFFSET(-1, ALLSELECTED(Data1[EQUIP_ID],Data1[EQUIP_STATUS_DATE],Data1[Stat Code],Data1[EQUIP_STATUS_REASON_DESC]), ORDERBY(Data1[EQUIP_STATUS_DATE]),,PARTITIONBY(Data1[EQUIP_ID])))&""

     

     

     

  • GA2's avatar
    GA2
    Regular Visitor
    3 years ago

    Thank you! However the formula gave me the same error 

     

    "Column 'Stat Code' in table 'TA_EQUIP_STATUS_HISTORY' cannot be found or may not be used in this expression."

     

    Which is why i was thinking it may have something to do with the column "stat code" being a calculated column. Can I use the offset function on a calculated column? If so, maybe i'm doing something else wrong.

     

    Appreciate you taking the time.