Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@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 , 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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |