Forum Discussion
Reference Previous Value in adjacent column based on multiple criteria using variables
Yes, this can be done in Power Query using the function that I've described here: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
If your data is clustered by priority already, you can use the integrated group-function (as it uses the fast GroupKind.Local). You would call the function with the following parameters:
fnTableReferenceDifferentRow(YourTableName, -1, {"Priority"}, {"OS_Number"})Just replace "YourTableName" by the name of your table and "fnTablReferenceDifferentRow" by the name of your function, if yu have named it differently.
I came close with variables using the below based on another post. I'm just having trouble making sure there is a filter on OS_Number.
Prior Version Priority =
VAR CurrentPriority =
SELECTEDVALUE ( OPENSEAT_Hist[OS Priority] )
VAR VersionID =
CALCULATE (
MIN ( OPENSEAT_Hist[OS_VERSION] ),
OPENSEAT_Hist[OS Priority] = CurrentPriority
)
VAR PriorPriority =
CALCULATE (
VALUES ( OPENSEAT_Hist[OS Priority] ),
OPENSEAT_Hist[OS_VERSION] = CurrentPriority - 1
)
RETURN
PriorPriorityDo you know if there would be another option in query editor maybe?
- Reach out to ImkeF, she's the M genius. For some reason the @ tagging isn't working for me.
Thanks Greg_Deckler! Looks like the @ worked on the post.
Yes, this can be done in Power Query using the function that I've described here: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
If your data is clustered by priority already, you can use the integrated group-function (as it uses the fast GroupKind.Local). You would call the function with the following parameters:
fnTableReferenceDifferentRow(YourTableName, -1, {"Priority"}, {"OS_Number"})Just replace "YourTableName" by the name of your table and "fnTablReferenceDifferentRow" by the name of your function, if yu have named it differently.
