Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
myou
Helper II
Helper II

How to write nested if statement based on condition from other table

 I have two tables 

OppTable

Opp id

statecodename

Actual close date

1

lost

5-7-2016

2

open

5-9-2015

3

won

10-5-2016

 

updatesstagechange Table

Custom_opportunityid

stage

createdon

nextstagestartdate

Oppstagerank

1

prospect

1-1-2016

2-1-2016

1

1

qualify

2-1-2016

5-7-2016(Actual close date from Opptable)

2

2

prospect

3-1-2016

4-1-2016

1

2

qualify

4-1-2016

5-1-2016

2

2

develop

5-1-2016

NOW DAte

3

3

Prospect

6-1-2016

7-1-2016

1

3

Qualify

7-1-2016

8-1-2016

2

3

develop

8-1-2016

10-5-2016(Actual close date from Opptable)

3

 

How to do it , if I want to make a condition if opportunity is lost or won, then the  next blank record in  updatesstagechange Table will take the actual close date from OppTable, else if open it takes today date

 

I wrote this column but its putting today date for all close, open and lost

 

NextStageStart date = IF(ISBLANK(LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)),NOW(),LOOKUPVALUE(Updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1))

 

OppStageRank = COUNTROWS( FILTER(

                                    updatesstagechange,

                                                     updatesstagechange[custom_opportunityid]<>""&&

                                              updatesstagechange[custom_opportunityid]=EARLIER(updatesstagechange[custom_opportunityid])&&

                                                     updatesstagechange[createdon]<EARLIER(updatesstagechange[createdon])

                                                          )

                                                  )+1

1 REPLY 1
v-sihou-msft
Employee
Employee

@myou

 

In your expression:

 

LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)

The current row "Updatesstagechange[Custom_opportunityid]" can never equal to "Updatesstagechange[Custom_opportunityid]+1". So your "NextStageStart date" will always return NOW().

 

 

Based on your description, you want to show different date based on Opp ID. Your expression should be like below:

 

NextStageStart date =
IF (
    updatesstagechange[Custom_opportunityid] = 2,
    NOW (),
    LOOKUPVALUE (
        Opp[Actual close date],
        Opp[Opp id], updatesstagechange[Custom_opportunityid]
    )
)

Regards,

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.