Forum Discussion

sleekpeek's avatar
sleekpeek
Regular Visitor
3 years ago

Formula Help Please

I have this formula in excel, i'd like to create the same in PBI.

 

=IF(W3<=-91,"Excluded",IF(W3>90,"Excluded",IF(W3>1,"Influenced",IF(W3<=0,"Sourced","Influenced")))

 

W3 in PBI would be: 'Opp Contact Role'[Days between]

 

how do I achieve that?

 

thanks

Need Help

4 Replies

    • confidentnwrong's avatar
      confidentnwrong
      Advocate I
      3 years ago

      If one of these work, please mark the answer as solution.
      Otherwise, provide more details, such as sample data of 'Opp Contact Role' without sensitive information.

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    pls try this
    create a new column

     

    Column = 
VAR _t1 ='Opp Contact Role'[Days between]
RETURN
SWITCH( TRUE(),
       _t1<=-91,"Excluded",
       _t1>90,"Excluded",
       _t1>1,"Influenced",
       _t1<=0,"Sourced","Influenced")

     

  • confidentnwrong's avatar
    confidentnwrong
    Advocate I
    3 years ago

    You need a way to aggregate 'Opp Contact Role'[Days between].

    One way of doing this is :

    =
var W3 = MIN('Opp Contact Role'[Days between])
RETURN
IF(W3<=-91
,"Excluded"
 ,IF(W3>90
 ,"Excluded"
  ,IF(W3>1
  ,"Influenced" 
   ,IF(W3<=0
   ,"Sourced"
   ,"Influenced")
  )
 )
)

     

    Depending on your data, MIN can be MAX, SUM, AVG,... 

    If you're 100% sure that there will be exactly 1 value that will show up using [Days Between], then all of these will return the same one.