Need Help

Question

Hi All,&nbsp;I have a data set like below.&nbsp; the last cilumn Strategy_Approval_status needs to be populated . The logic is if strategy_name has Approved in Task_name_approval_status then that Strategy is Approved, If it does not have approved and has only 'Awaiting approval' Then it is 'awaiting approval', and&nbsp; if the sttaus is 'Not Approved ' then it will be Not Approved. I have populated the columns accordingly. &nbsp; It is not possible to do a if condition on Strategy_Name as there are numerous values of it.&nbsp;&nbsp;Is it possible to calculate Strategy_Approval_Status then? Please help&nbsp;Strategy_Name&nbsp; Task_Name Task_Approval_Status Date_Created USD_Value Strategy_Approval_Status(needs to be calculated)STR1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage1 &nbsp; &nbsp; &nbsp;&nbsp; Awaiting Approval &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; ApprovedSTR1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage2 &nbsp; &nbsp; &nbsp;&nbsp; Awaiting Approval &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STR1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage3 &nbsp; &nbsp; &nbsp;&nbsp; Approved &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100STR2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage1 &nbsp; &nbsp; &nbsp;&nbsp; Awaiitng Approval &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Awating ApprovalSTR2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage2 &nbsp; &nbsp; &nbsp;&nbsp; Awaiting Approval &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200STR3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage 0 &nbsp; &nbsp; &nbsp; Not Approved &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 600 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Not ApprovedSTR4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage2 &nbsp; &nbsp; &nbsp;&nbsp; Awaiting Approval &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 400 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; ApprovedSTR4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Stage3 &nbsp; &nbsp; &nbsp;&nbsp; Approved &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 400&nbsp;Thanks in advance!!&nbsp;RegardsAnkhi

rajulshah · Accepted Answer

Hello&nbsp;Anonymous,&nbsp;Please use the following DAX and let me know if this is what you want:Latest Stage = 
VAR LatestStage = ADDCOLUMNS(Strategy,"RankbyStage",COUNTX(FILTER(ALL(Strategy),Strategy[Strategy_Name] = EARLIER(Strategy[Strategy_Name]) &amp;&amp; Strategy[Task_Name]&lt; EARLIER(Strategy[Task_Name])),Strategy[Strategy_Name])+1)
VAR MaxValue = MAXX(FILTER(LatestStage,[Strategy_Name]=EARLIER([Strategy_Name])),[RankbyStage])
RETURN MINX(FILTER(LatestStage,[RankbyStage]=MaxValue &amp;&amp; Strategy[Strategy_Name]=EARLIER(Strategy[Strategy_Name])),Strategy[Task_Approval_Status])&nbsp;Hope this helps.&nbsp;

Forum Discussion

Need Help

2 Replies