Path reverse lookup

[smpa01]

Is there any way to achieve a reverse look up that returns the memeber position on the path?

I have this

| rowNum | pathString       | individiualValue |
|--------|------------------|------------------|
| 1      | 6000|5000|4000   | 6000             |
| 1      | 6000|5000|4000   | 4000             |
| 2      | 8000|9000|7000   | 1000             |
| 3      | 8000|9000|7000   | 8000             |

I want to end up with this

| rowNum | pathString       | individiualValue | position |
|--------|------------------|------------------|----------|
| 1      | 6000|5000|4000   | 6000             | 1        |
| 1      | 6000|5000|4000   | 4000             | 3        |
| 2      | 8000|9000|7000   | 1000             |          |
| 3      | 8000|9000|7000   | 8000             | 1        |

I can currently achieve it with this

Table = 
VAR baseTbl =
    DATATABLE (
        "rowNum", INTEGER,
        "pathString", STRING,
        "individiualValue", STRING,
        {
            { 1, "6000|5000|4000", "6000" },
            { 1, "6000|5000|4000", "4000" },
            { 2, "8000|9000|7000", "1000" },
            { 3, "8000|9000|7000", "8000" }
        }
    )
VAR modTbl =
    SUMMARIZE ( baseTbl, [rowNum], [pathString] )
VAR lookupTbl =
    GENERATE (
        modTbl,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [pathString] ) ),
            "ind", PATHITEM ( [pathString], [Value], TEXT )
        )
    )
VAR res =
    GENERATEALL (
        baseTbl,
        VAR ky1 = [rowNum]
        VAR ky2 = [pathString]
        VAR ky3 = [individiualValue]
        RETURN
            SELECTCOLUMNS (
                FILTER ( lookupTbl, [rowNum] = ky1 && [pathString] = ky2 && [ind] = ky3 ),
                "position", [Value]
            )
    )
RETURN
    res

 But is there a built-in syntax available to tackle this or any other optimized way?

@AlexisOlson  @CNENFRNL 

[AlexisOlson · Accepted solution]

Try this as a calculated column:

position = 
MINX (
    FILTER (
        GENERATESERIES ( 1, PATHLENGTH ( [pathString] ) ),
        PATHITEM ( [pathString], [Value] ) = [individualValue]
    ),
    [Value]
)

[smpa01]

👍

[jdbuchanan71]

@smpa01 

If the length of the string you are looking for is always the same can you do it mathematically?

Position = 
VAR _Search = SEARCH('Table'[individiualValue],'Table'[pathString],1,BLANK())
RETURN IF ( NOT ISBLANK ( _Search ), ( _Search - 1 ) / 5 + 1 )

[CNENFRNL]

Hi, my friend, List.PositionOf() from PQ asked me to pass a message on to you, this task is totally a light weight for it.

[smpa01]

Appreciate !!! but I need a DAX solution.