Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dax Returned statement Help

ok so the top had of my DAx is running fine as it should but then I get down to the return part I am running into issues. I am trying to have it print off a 2,1,-1or -2 to based off what it get from ...
"DAX help"
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Hi Anonymous ,

     

    You can refer to below method.

     

     

    RETURN = 
VAR LatestStatus = 'Table'[Latest Status]
VAR SplitPositions = 
    UNION(
        SELECTCOLUMNS({0},"Position",[Value]),
        DISTINCT(FILTER(SELECTCOLUMNS(GENERATESERIES(1,LEN(LatestStatus )),"Position",FIND(",",LatestStatus ,[Value],BLANK())),[Position]<>BLANK())),
        {LEN(LatestStatus )+1}
    )
VAR SplitPosition_AddIndex = ADDCOLUMNS(SplitPositions,"Index",RANKX(SplitPositions,[Position],,1))
VAR NumList = 
    SELECTCOLUMNS(
        GENERATESERIES(1,COUNTROWS(SplitPositions)-1),
        "Num",
            VAR Start_ = SUMMARIZE(FILTER(SplitPosition_AddIndex,[Index]=[Value]),[Position])+1
            VAR End_ = SUMMARIZE(FILTER(SplitPosition_AddIndex,[Index]=[Value]+1),[Position])
            RETURN
            MID(LatestStatus ,Start_,End_-Start_)*1
    )
RETURN
MAXX(NumList,[Num])

     

     

    Demo - Dax Returned statement Help.pbix

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

     

xifeng_L's avatar
xifeng_L
Super User
2 years ago

Hi Anonymous ,

 

You can refer to below method.

 

 

RETURN = 
VAR LatestStatus = 'Table'[Latest Status]
VAR SplitPositions = 
    UNION(
        SELECTCOLUMNS({0},"Position",[Value]),
        DISTINCT(FILTER(SELECTCOLUMNS(GENERATESERIES(1,LEN(LatestStatus )),"Position",FIND(",",LatestStatus ,[Value],BLANK())),[Position]<>BLANK())),
        {LEN(LatestStatus )+1}
    )
VAR SplitPosition_AddIndex = ADDCOLUMNS(SplitPositions,"Index",RANKX(SplitPositions,[Position],,1))
VAR NumList = 
    SELECTCOLUMNS(
        GENERATESERIES(1,COUNTROWS(SplitPositions)-1),
        "Num",
            VAR Start_ = SUMMARIZE(FILTER(SplitPosition_AddIndex,[Index]=[Value]),[Position])+1
            VAR End_ = SUMMARIZE(FILTER(SplitPosition_AddIndex,[Index]=[Value]+1),[Position])
            RETURN
            MID(LatestStatus ,Start_,End_-Start_)*1
    )
RETURN
MAXX(NumList,[Num])

 

 

Demo - Dax Returned statement Help.pbix

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~