Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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_DATE | EQUIP_ID | CURRENT_FAC_ID | Stat Code | OFFSET | EQUIP_STATUS_REASON_DESC |
2/26/2023 14:39 | 281683 | 14 | Status 1 | Status 2.1 | |
2/25/2023 16:01 | 281683 | 14 | Status 2.1 | Status 1 | Miscellaneous |
2/16/2023 19:28 | 281683 | 14 | Status 1 | Status 2.2 | |
2/15/2023 16:56 | 281683 | 14 | Status 2.2 | Status 3 | Scheduled Maint |
2/14/2023 5:47 | 281683 | 14 | Status 3 | Status 1 | Mileage |
2/11/2023 6:21 | 281683 | 14 | Status 1 | Status 2.1 | |
2/10/2023 15:12 | 281683 | 14 | Status 2.1 | Miscellaneous | |
3/5/2023 14:00 | 281682 | 14 | Status 3 | Status 2.1 | Mileage |
3/3/2023 17:54 | 281682 | 14 | Status 2.1 | Status 3 | Miscellaneous |
3/3/2023 13:46 | 281682 | 14 | Status 3 | Mileage | |
2/18/2023 9:32 | 281681 | 24 | Status 5 | Status 5 | Warranty |
2/18/2023 9:32 | 281681 | 24 | Status 5 | Status 5 | Warranty |
2/18/2023 9:32 | 281681 | 24 | Status 5 | Status 2.1 | Warranty |
2/18/2023 9:32 | 281681 | 24 | Status 2.1 | Running Repair | |
3/2/2023 21:16 | 277561 | 14 | Status 2.1 | Status 2.1 | Running Repair |
3/2/2023 21:16 | 277561 | 14 | Status 2.1 | Status 1 | Running Repair |
2/28/2023 18:57 | 277561 | 14 | Status 1 |
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())
)
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?
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.
@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])))&""
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |