Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Try this as a calculated column:
position =
MINX (
FILTER (
GENERATESERIES ( 1, PATHLENGTH ( [pathString] ) ),
PATHITEM ( [pathString], [Value] ) = [individualValue]
),
[Value]
)
Try this as a calculated column:
position =
MINX (
FILTER (
GENERATESERIES ( 1, PATHLENGTH ( [pathString] ) ),
PATHITEM ( [pathString], [Value] ) = [individualValue]
),
[Value]
)
👍
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 )
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Appreciate !!! but I need a DAX solution.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |