Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Tricky if formula - DAX error next step

Hi Experts

 

I have the following if formula which works upto a certan point...

 

TWC = IF(Apple[SourceAmt] =1 , "TWC",
IF(AND(Apple[SourceAmt] =1, Apple[trust] > 1000000) , "VB3",

IF(AND(Apple[SourceAmt] =1, Apple[trust] > 100000 && Apple[Trust] < 1000000), "VB2A",

IF(AND(Apple[SourceAmt] =1, Apple[trust] > 25000 && Apple[Trust] < 100000), "VB3A", "VB1")))), 

i want to add the following step when Apple[SourceAmt] = 0

IF(AND(Apple[SourceAmt] =0, orange[trust] > 1000000) , "VB4",

IF(AND(Apple[SourceAmt] =0, orange[trust] > 100000 && orange[Trust] < 1000000), "VB5A",

IF(AND(Apple[SourceAmt] =0, orange[trust] > 25000 && orange[Trust] < 100000), "VB6A", "VB1A")))), 






Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    4 years ago

    Hi Anonymous ,

     

    Since you are referencing columns from two different tables, it is recommended taht you use measures and use SWITCH function.

     

    Please try following DAX:

    Measure = SWITCH(
    TRUE(),
    MAX(Apple[SourceAmt]) =1 && MAX(Apple[trust]) > 1000000, "VB3",
    MAX(Apple[SourceAmt]) =1 && MAX(Apple[trust]) > 100000 && MAX(Apple[trust]) < 1000000, "VB2A",
    MAX(Apple[SourceAmt]) =1 && MAX(Apple[trust]) > 25000 && MAX(Apple[Trust]) < 100000, "VB3A",
    MAX(Apple[SourceAmt]) =0 && MAX(orange[trust]) > 1000000 , "VB4",
    MAX(Apple[SourceAmt]) =0 && MAX(orange[trust]) > 100000 && MAX(orange[Trust]) < 1000000, "VB5A",
    MAX(Apple[SourceAmt]) =0 && MAX(orange[trust]) > 25000 && MAX(orange[Trust]) < 100000, "VB6A")

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Since you are referencing columns from two different tables, it is recommended taht you use measures and use SWITCH function.

     

    Please try following DAX:

    Measure = SWITCH(
    TRUE(),
    MAX(Apple[SourceAmt]) =1 && MAX(Apple[trust]) > 1000000, "VB3",
    MAX(Apple[SourceAmt]) =1 && MAX(Apple[trust]) > 100000 && MAX(Apple[trust]) < 1000000, "VB2A",
    MAX(Apple[SourceAmt]) =1 && MAX(Apple[trust]) > 25000 && MAX(Apple[Trust]) < 100000, "VB3A",
    MAX(Apple[SourceAmt]) =0 && MAX(orange[trust]) > 1000000 , "VB4",
    MAX(Apple[SourceAmt]) =0 && MAX(orange[trust]) > 100000 && MAX(orange[Trust]) < 1000000, "VB5A",
    MAX(Apple[SourceAmt]) =0 && MAX(orange[trust]) > 25000 && MAX(orange[Trust]) < 100000, "VB6A")

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ,

     

    Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

     

    Best regards,

    Yadong Fang