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.
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.
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
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
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
Do you know if there would be another option in query editor maybe?
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I was just reading your page now! I'll work through it and let you know how it goes. thank you @ImkeF!
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |