Need Help

[Anonymous]

Hi All,

I have a data set like below.  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  if the sttaus is 'Not Approved ' then it will be Not Approved. I have populated the columns accordingly.   It is not possible to do a if condition on Strategy_Name as there are numerous values of it. 

Is it possible to calculate Strategy_Approval_Status then? Please help

Strategy_Name  Task_Name Task_Approval_Status Date_Created USD_Value Strategy_Approval_Status(needs to be calculated)

STR1                    Stage1        Awaiting Approval                             100            Approved

STR1                    Stage2        Awaiting Approval                             100            

STR1                    Stage3        Approved                                           100

STR2                    Stage1        Awaiitng Approval                             200           Awating Approval

STR2                    Stage2        Awaiting Approval                             200

STR3                    Stage 0       Not Approved                                    600          Not Approved

STR4                    Stage2        Awaiting Approval                              400          Approved

STR4                    Stage3        Approved                                            400

Thanks in advance!!

Regards

Ankhi

[rajulshah · Accepted solution]

Hello @Anonymous,

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]) && Strategy[Task_Name]< 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 && Strategy[Strategy_Name]=EARLIER(Strategy[Strategy_Name])),Strategy[Task_Approval_Status])

Hope this helps. 

[Anonymous]

Thank you so so much..

It worked !!!