Tricky if formula - DAX error next step

[Anonymous]

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")))), 

[v-yadongf-msft]

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

[v-yadongf-msft · Accepted solution]

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.