Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to write an IF AND statement and think my logic might be off.. The first condition is that PPAutoOnwers need to equal '1'. The second condition has multiple conditions, and I've tried to nest them here. If both of these conditions aren't met, then I'd like a BLANK returned.
Here's what I have so far
Auto Owners = IF(AND('Actual Revenue'[PPAutoOwners]="1", IF('Actual Revenue'[PolicyAndLineTypes]="HOME",'Actual Revenue'[LineEstimatedPremium]*.19,IF('Actual Revenue'[PolicyAndLineTypes]="PPKG/HOME",'Actual Revenue'[LineEstimatedPremium]*.19,IF('Actual Revenue'[PolicyAndLineTypes]="AUTO",'Actual Revenue'[LineEstimatedPremium]*.16,IF('Actual Revenue'[PolicyAndLineTypes]="PUMB",'Actual Revenue'[LineEstimatedPremium]*.16,IF('Actual Revenue'[PolicyAndLineTypes]="PPKG/INLM",'Actual Revenue'[LineEstimatedPremium]*.19,BLANK())))))
Solved! Go to Solution.
please try with this:
Auto Owners = IF ( 'Actual Revenue'[PPAutoOwners] = "1", SWITCH ( 'Actual Revenue'[PolicyAndLineTypes], "HOME", 'Actual Revenue'[LineEstimatedPremium] * .19, "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .19, "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .16, "PUMB", 'Actual Revenue'[LineEstimatedPremium] * .16, "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .19 ), BLANK () )
Let me know if helps
@Caitlin_Knox wrote:Am I correct in my syntax below if I want to use .15 for all else that still meet the first condition?
So the Premium Payable Code would still need to equal CHUIN2,
but if the Policy and LIne type is different that what is defined in the expression,
then the line estimated premium should be multiplied by .15 as a 'default'
Yes! You Got it!
You will get values only if Premium Payable Code is CHUIN2 ! All other Premium Paybale Codes will be BLANK()
then ALL Policy and Line Types if they match the ones you've listed in the SWITCH will be multiplied accordingly
and if they do not match any of the 5 you listed in the SWITCH will be multiplied by 0.15
Great job!
please try with this:
Auto Owners = IF ( 'Actual Revenue'[PPAutoOwners] = "1", SWITCH ( 'Actual Revenue'[PolicyAndLineTypes], "HOME", 'Actual Revenue'[LineEstimatedPremium] * .19, "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .19, "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .16, "PUMB", 'Actual Revenue'[LineEstimatedPremium] * .16, "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .19 ), BLANK () )
Let me know if helps
Maybe you can help with one more, related reqeust? Am I correct in my syntax below if I want to use .15 for all else that still meet the first condition? So the Premium Payable Code would still need to equal CHUIN2, but if the Policy and LIne type is different that what is defined in the expression, then the line estimated premium should be multiplied by .15 as a 'default'
Chubb = IF ( 'Actual Revenue'[PremiumPayableCode]="CHUIN2", SWITCH ( 'Actual Revenue'[PolicyAndLineTypes], "HOME", 'Actual Revenue'[LineEstimatedPremium] * .25, "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .25, "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .25, "PPKG/AUTO", 'Actual Revenue'[LineEstimatedPremium] *.25, "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .20 ,'Actual Revenue'[LineEstimatedPremium]*.15 ), BLANK () )
@Caitlin_Knox wrote:Am I correct in my syntax below if I want to use .15 for all else that still meet the first condition?
So the Premium Payable Code would still need to equal CHUIN2,
but if the Policy and LIne type is different that what is defined in the expression,
then the line estimated premium should be multiplied by .15 as a 'default'
Yes! You Got it!
You will get values only if Premium Payable Code is CHUIN2 ! All other Premium Paybale Codes will be BLANK()
then ALL Policy and Line Types if they match the ones you've listed in the SWITCH will be multiplied accordingly
and if they do not match any of the 5 you listed in the SWITCH will be multiplied by 0.15
Great job!
Thank you!!
Yep that's what I came up with too - except with SWITCH ( TRUE () - you have to repeat the column name
Auto Owners = IF ( 'Actual Revenue'[PPAutoOwners] = "1", SWITCH ( TRUE (), 'Actual Revenue'[PolicyAndLineTypes] = "HOME", 'Actual Revenue'[LineEstimatedPremium] * .19, 'Actual Revenue'[PolicyAndLineTypes] = "PPKG/HOME", 'Actual Revenue'[LineEstimatedPremium] * .19, 'Actual Revenue'[PolicyAndLineTypes] = "AUTO", 'Actual Revenue'[LineEstimatedPremium] * .16, 'Actual Revenue'[PolicyAndLineTypes] = "PUMB", 'Actual Revenue'[LineEstimatedPremium] * .16, 'Actual Revenue'[PolicyAndLineTypes] = "PPKG/INLM", 'Actual Revenue'[LineEstimatedPremium] * .19, BLANK () ), BLANK () )
@Vvelardeis lightning fast
One approach I like is in not nesting. Set each statement in its own column - i.e.
IF('Actual Revenue'[PolicyAndLineTypes]="HOME",'Actual Revenue'[LineEstimatedPremium]*.19,0)
This way you can then see each calculation in each column (or 0)
Then add a final calculation column that sums or selects which column is valid (depending on what you seek)
As you've discovered nesting beyond a couple levels becomes a pain to trouble shoot. This method allows you to see each step of the chain of logic.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |