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
GA2
Regular Visitor

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  
4 REPLIES 4
Sahir_Maharaj
Super User
Super User

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())
)

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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
GA2
Regular Visitor

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.

amitchandak
Super User
Super User

@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])))&""

 

 

amitchandak_0-1678415188333.png

 

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.