Forum Discussion

Michele78256's avatar
Michele78256
Regular Visitor
3 years ago
Solved

How to replicate Excel formula in Power BI with multiple IF/AND criteria

I am trying to replicate this Excel formula in Power BI and am having trouble. The end result needs to be a whole number, which ultimately is the difference between two dates: =IF(AND(U2<>"", CL2<...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Michele78256 , try like, use column names in power bi

    =IF(([U]<>"" && [CL]<>""&& [C]="Retail"), ([CL]-[U])-[BZ], IF(([V]<>"" && [CL]<>""&& [C]="Correspondent"), [W]-[V], ""))

     

    if the values are blan, not "", then you can check isblank([Column name])

    or

    not(isblank([column Name]) )

amitchandak's avatar
amitchandak
Super User
3 years ago

Michele78256 , try like, use column names in power bi

=IF(([U]<>"" && [CL]<>""&& [C]="Retail"), ([CL]-[U])-[BZ], IF(([V]<>"" && [CL]<>""&& [C]="Correspondent"), [W]-[V], ""))

 

if the values are blan, not "", then you can check isblank([Column name])

or

not(isblank([column Name]) )

Michele78256's avatar
Michele78256
Regular Visitor
3 years ago

Thank you for your time and input! I did have to make some modifications to what you suggested, but you definitely put me on th right track with using && for AND and NOT(ISBLANK) instead of "". Here is what worked:
App2FundDaysCalc = IF(NOT(ISBLANK('LBI Mortgage Export'[Application]))&&NOT(ISBLANK('LBI Mortgage Export'[Funding Date]))&&'LBI Mortgage Export'[Channel]="Retail",('LBI Mortgage Export'[Funding Date]-'LBI Mortgage Export'[Application]-'LBI Mortgage Export'[Total_Hold_Days]),IF(NOT(ISBLANK('LBI Mortgage Export'[App_Sent_to_Processing]))&&NOT(ISBLANK('LBI Mortgage Export'[Funding Date]))&&'LBI Mortgage Export'[Channel]="Correspondent",('LBI Mortgage Export'[Approved]-'LBI Mortgage Export'[App_Sent_to_Processing]),0))