Forum Discussion

donovanm's avatar
donovanm
Frequent Visitor
1 year ago
Solved

IF AND Multiple Conditions

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"
)))
Need Help
  • donovanm's avatar
    donovanm
    1 year ago

    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.

     

     

4 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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.

  • wini_R's avatar
    wini_R
    Solution Supplier
    1 year ago

    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"
  • donovanm's avatar
    donovanm
    Frequent Visitor
    1 year ago

    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.

     

     

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    1 year ago

    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

        )

    1. 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.
    2. Validate the results by adding columns for ContractID, Lead/Follow, Total Premium, and the Category Measure to your visual.
    3. 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.