Forum Discussion
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<>"", C2="Retail"), (CL2-U2)-BZ2, IF(AND(V2<>"", CL2<>"", C2="Correspondent"), W2-V2, ""))
All the fields referenced are date fields, except C is text. Might be important to note that CL is a custom calc date field.
Part of the problem I am having is Power BI limits IF statements to two conditions, and also does not seem to like comparing dates to text:
I also tried this with just the first part of the IF statement and it didn't like this, either:
Looking for guidance on how to proceed. I'm stuck. Thank you in advance for any advice you have!
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]) )
2 Replies
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]) )
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))
