Forum Discussion
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 "latest status" which hold two values. I need hep because I need to change itto catch certain combos like if "latest status" is -2,2 & 2,-2 & 2 ,2 & 2,1 and so on. As long as there is one 2 present it print 2 and same with 1 also lons as 1 is the greatest value it print 1 Examples if its 1,-1& -1,1 &1,-2 & -2,1 it prints 1 and for combos like -1,-1 it print -1 and for -2 -2 it prints -2. tried switching the and to or but it let all combos through.
RETURN
IF(
FIND("2", LatestStatus, 1, 0) > 0 && FIND("-2", LatestStatus, 1, 0) = 0, "2",
IF(
FIND("1", LatestStatus, 1, 0) > 0 && FIND("-1", LatestStatus, 1, 0) = 0, "1",
IF(
FIND("-2", LatestStatus, 1, 0) > 0, "-2",
IF(
FIND("-1", LatestStatus, 1, 0) > 0, "-1",
"BLANK"
)
)
)
)
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~
3 Replies
Hi Anonymous
Would this help?
RETURN IF( FIND( "-2", LatestStatus, 1, 0 ) > 0, "-2", IF( FIND( "-1", LatestStatus, 1, 0 ) > 0, "-1", IF( FIND( "2", LatestStatus, 1, 0 ) > 0, "2", IF( FIND( "1", LatestStatus, 1, 0 ) > 0, "1", "BLANK" ) ) ) )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~
