IF AND Multiple Conditions

[donovanm]

Hi,

I need to create a column where I end up with four possible outcomes based on two conditions relating to various contracts status and the corresponding premium figure.

In Excel it would be IF(AND(B2="Lead",C2>=250000),"1",IF(AND(B2="Lead",C2<250000),"2","tbc")) based on the following:

Lead > £250k = Category 1

Lead < £250k = Category 2

Follow > £250k = Category 3

Follow < £250k = Category 4

I have written the following Syntax, which does not give me an error but I am not getting the results I require.  Any help would be appreciated.

Category = IF('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]="Lead" &&

SUM('Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare])>=250000,"1",

IF('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]="Lead" &&

SUM('Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare])<249999,"2",

IF('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]="Follow" &&

SUM('Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare])>=250000,"3","4"

)))

[v-hashadapu]

Hi @donovanm , Thank you for reaching out to the Microsoft Community Forum.

The issue is that your DAX formula is being applied at the row level (monthly premium), whereas you need the category to be determined based on the total premium per contract. To fix this, you should create a measure instead of a calculated column, as measures can dynamically calculate the total premium across months.

Please try this:

1. Create a measure:

Category Measure =

VAR TotalPremium =

    CALCULATE(

        SUM('Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare]),

        ALLEXCEPT('Analytics vwSnapshotEstimatedPremiumsv2', 'Analytics vwSnapshotEstimatedPremiumsv2'[ContractID])

    )

RETURN

    SWITCH(

        TRUE(),

        SELECTEDVALUE('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]) = "Lead" && TotalPremium >= 250000, 1,

        SELECTEDVALUE('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]) = "Lead" && TotalPremium < 250000, 2,

        SELECTEDVALUE('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]) = "Follow" && TotalPremium >= 250000, 3,

        4

    )

2. Use this measure in your table or matrix visual. Ensure that ContractID (or an equivalent unique contract identifier) is part of your visual to group data correctly.
3. Validate the results by adding columns for ContractID, Lead/Follow, Total Premium, and the Category Measure to your visual.
4. Verify that the categorization is correct based on the total premium: If Total Premium >= 250K and Lead, it should show Category 1, If Total Premium < 250K and Lead, it should show Category 2, If Total Premium >= 250K and Follow, it should show Category 3, If Total Premium < 250K and Follow, it should show Category 4.

Thank you @FarhanJeelani for your prompt and appropriate answer.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[donovanm · Accepted solution]

Hi @FarhanJeelani 

Thank you for providing that solution.  I believe that the aggregation is causing the problem here as the data has the premium monthly so the category DAX is being applied at that level.  Ideally I would like to be able to total the months up and then apply the Category to the total.

e.g. The contracy below has a Total Premium of 968k but it is classfied as Lead and over the 250k threshold yet it is has a Category 2 - I assume because the monthly value is 81k.

[wini_R]

Hey @donovanm,

If you want to add a column in Powe Query you can try the following formula:

if [B2] = "Lead" then 
 if [C2] >= 250000 then "Category 1" else "Category 2"
else if [C2] >= 250000 then "Category 3" else "Category 4"

[FarhanJeelani]

Dear @donovanm ,

Your DAX formula has a couple of issues:

SUM inside an IF condition – Since you're using SUM(), it aggregates values, which is not ideal for a calculated column. A calculated column works row-by-row and should reference individual row values instead.

Incorrect boundary for < 250000 – You have SUM(... < 249999), but it should be < 250000.
Data type mismatch – The category outputs should ideally be numeric, not strings ("1", "2", etc.).

Corrected DAX for a Calculated Column
Use this formula in Power BI calculated column (not a measure):

Category =
SWITCH(
TRUE(),
'Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow] = "Lead" &&
'Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare] >= 250000, 1,

'Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow] = "Lead" &&
'Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare] < 250000, 2,

'Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow] = "Follow" &&
'Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare] >= 250000, 3,

4
)

Please mark this post as solution if it helps you. Appreciate Kudos.