Forum Discussion

Aucesar's avatar
Aucesar
Icon for Helper III rankHelper III
3 years ago
Solved

IF Multiples Statements in DAX

Hi Everyone;

 

I need help to calculate commision based on production defects as below:

 

IF production category "A" and is below 1% comission equals FULL, if between 1% and 2% Comission equals HALF, above 25 NONE,

IF production category "B" and is below 1% comission equals FULL, above NONE

IF production category "C" and is below 5% comission equals FULL, above NONE

 

Category is a column with Text value, % is measure.

 

Tried some replies but can´t figure out AND´s IF´s I need to use...

Need Help
  • mlsx4's avatar
    mlsx4
    3 years ago

    You can use a switch too. Something like this:

     

    Commision= 
SWITCH(
 SELECTEDVALUE(Table[Category]),
 "A", IF ([Value] <0.01,"FULL", IF([Value] >=0.01 && [Value] <=0.02,"HALF", IF([Value]  >0.25,"NONE"))),
 "B", IF ([Value]  <0.01,"FULL", "NONE"),
 "C", IF ([Value]  <0.05,"FULL","NONE")
)

     

6 Replies

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable Member
    3 years ago

    You can use a switch too. Something like this:

     

    Commision= 
SWITCH(
 SELECTEDVALUE(Table[Category]),
 "A", IF ([Value] <0.01,"FULL", IF([Value] >=0.01 && [Value] <=0.02,"HALF", IF([Value]  >0.25,"NONE"))),
 "B", IF ([Value]  <0.01,"FULL", "NONE"),
 "C", IF ([Value]  <0.05,"FULL","NONE")
)

     

    • Aucesar's avatar
      Aucesar
      Icon for Helper III rankHelper III
      3 years ago

      Dear mlsx4 

      Worked as expected, thank YOU. Just another help, how to count "FULL" / "HALF" / "NONE" did a try using countrows and countx but they show more results than I have. Thanks...

      • mlsx4's avatar
        mlsx4
        Icon for Memorable Member rankMemorable Member
        3 years ago

        Hi Aucesar 

        Have you tried with the following for each?

         

        Numb=
var half= CALCULATE(COUNTROWS(TABLE),Commision="HALF")
var none= CALCULATE(COUNTROWS(TABLE),Commision="NONE")
var full= CALCULATE(COUNTROWS(TABLE),Commision="FULL")

RETURN half+none+full

         

        Also, how have you created the value Commision as a column or as a measure?

        If it is a column you can do:

         

        all numb= CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table, Table[Commision] ) )