Virtual Column to Row Numbering based on distance

[lochtefeldm]

Hi All,

Trying to achieve an expression that calculates a counting number based on where the row falls in a list using calculated distance where lower distance values should be high in the list. Need a virtual column since the usersettings filter rows based on a user inputted zip code. 

[Suvrutt_Gurjar · Accepted solution]

Using any multi row expressions in a virtual column will lead to potential sync time increase.  With this in mind you could try below.

1. Please create a slice on the table called say "Specific_Zipcode'" with an expression something like USERSETTINGS("ZipCode") =[Zipcode]

2. Then the expression for ranking the distances in the virtual column can be something like 

COUNT(
  SPLIT(
    ANY(
      SPLIT(
        (" , " & Specific_Zipcode[Distance] & " , "),
        (" , " & [Distance] & " , ")
      )
    ),
    " , "
  )
)

The expression is based on the following tip 

INDEXOF() - Google Cloud Community

If the table is a child table to some other table, we could try the possibility of using system generated reverse reference column instead of slice based list to reduce the sync time impact.

[lochtefeldm]

Thanks, I used what you gave and ultimately was able to get things working to number them by distance with the expression below. Much appreciated. 

COUNT(
    SELECT(
        nursing home profile data[CMS Certification Number],  
        [User Settings Distance Calculation] <= [_THISROW].[User Settings Distance Calculation]
    )
)

Numbering let me apply different formatting to pins & correlate with a generated pdf.

[Suvrutt_Gurjar]

You are welcome. Thank you for the update. Nice to know you have got it working.