Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

TRICKY if formula

Hi Experts, 

 

The first part of my if formula works fine - how do i add the condition if [quoteman] = 0 (SEE BOLD SECTION)

DAX Measure

If(QUOTEMAN[OPT] = 1, IF(QUOTEMAN[OPTCOST] > 1000000 "VB3",
IF(AND(QUOTEMAN[OPTCOST] > 100000, QUOTEMAN[OPTCOST] < 1000000, "VB2",
IF(ANDQUOTEMAN[OPTCOST] > 25000, QUOTEMAN[OPTCOST] <100000, "VB1", "vb")))
||
IF(QUOTEMAN[OPT] = 0, 
IF(QUOTEMAN[AMT] > 1000000, "VB3",
IF(AND(QUOTEMAN[AMT] > 100000, QUOTEMAN[AMT] < 1000000, "VB2",
IF(ANDQUOTEMAN[AMT] > 25000, QUOTEMAN[AMT] <100000, "VB1", "vb")))

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Anonymous,

     

    This should work as a calculated column. If you want a measure, wrap each column reference in an aggregate function like SUM.

     

    Calculated Column =
SWITCH (
    QUOTEMAN[OPT],
    1,
        SWITCH (
            TRUE,
            QUOTEMAN[OPTCOST] > 1000000, "VB3",
            QUOTEMAN[OPTCOST] > 100000
                && QUOTEMAN[OPTCOST] < 1000000, "VB2",
            QUOTEMAN[OPTCOST] > 25000
                && QUOTEMAN[OPTCOST] < 100000, "VB1",
            "vb"
        ),
    0,
        SWITCH (
            TRUE,
            QUOTEMAN[AMT] > 1000000, "VB3",
            QUOTEMAN[AMT] > 100000
                && QUOTEMAN[AMT] < 1000000, "VB2",
            QUOTEMAN[AMT] > 25000
                && QUOTEMAN[AMT] < 100000, "VB1",
            "vb"
        )
)

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    Anonymous,

     

    This should work as a calculated column. If you want a measure, wrap each column reference in an aggregate function like SUM.

     

    Calculated Column =
SWITCH (
    QUOTEMAN[OPT],
    1,
        SWITCH (
            TRUE,
            QUOTEMAN[OPTCOST] > 1000000, "VB3",
            QUOTEMAN[OPTCOST] > 100000
                && QUOTEMAN[OPTCOST] < 1000000, "VB2",
            QUOTEMAN[OPTCOST] > 25000
                && QUOTEMAN[OPTCOST] < 100000, "VB1",
            "vb"
        ),
    0,
        SWITCH (
            TRUE,
            QUOTEMAN[AMT] > 1000000, "VB3",
            QUOTEMAN[AMT] > 100000
                && QUOTEMAN[AMT] < 1000000, "VB2",
            QUOTEMAN[AMT] > 25000
                && QUOTEMAN[AMT] < 100000, "VB1",
            "vb"
        )
)