Reference Previous Value in adjacent column based on multiple criteria using variables

[GTPowerBIUser]

I'd like to stay away from EARLIER() since my table is large and that may create to many behind the scenes calculations. I'm hoping for a solution using variables.

Based on the below table, I'm looking to reference the Prior Priority based on the OS_Number. Prior Priority would be the OS_Version - 1. If Version is 1 the new column should show null or NA.

[Ashish_Mathur]

Hi,

This works

let
    Source = Excel.CurrentWorkbook(){[Name="Data1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prior Priority", type text}, {"OS_Number", Int64.Type}, {"OS_version", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Next value" = Table.AddColumn(#"Added Index", "custom column", each #"Added Index"{[Index]}[Prior Priority]),
    #"Added Custom" = Table.AddColumn(#"Next value", "Custom", each if [custom column]="NA" then [Prior Priority] else [custom column]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"custom column"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Priority"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Priority", "Prior Priority", "OS_Number", "OS_version", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns1"

[Kristjan76]

Hi there,

You can try this, I hope that understood what you are trying to achive.

New Column (Prior Priority) = 
IF(
  Table[OS_Version] > 1,
  VAR osNr = Table[OS_Number]
  VAR osVer = Table[OS_Version]-1
  RETURN
  MAXX(
    FILTER(
      Table,
      Table[OS_Number] = osNr
      && Table[OS_Version] = osVer
    ),
    Table[Priority]
  )
)

If this works for you please LIKE and mark as solution.

Regards,

Kristjan76

[GTPowerBIUser]

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
    PriorPriority

[GTPowerBIUser]

Do you know if there would be another option in query editor maybe?

[Greg_Deckler]

Reach out to @ImkeF, she's the M genius. For some reason the @ tagging isn't working for me.

[GTPowerBIUser]

Thanks @Greg_Deckler! Looks like the @ worked on the post.

[ImkeF · Accepted solution]

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-po...

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.

[GTPowerBIUser]

I was just reading your page now! I'll work through it and let you know how it goes. thank you @ImkeF!

[Greg_Deckler]

Not sure how you are going to avoid using EARLIER unless you do it in Power Query maybe.