Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Matrix Visual - creating a measure with IF statement

Hi all!   I am back with more questions.   This time, I want to ask whether it is possible to a make possible to incorporate an IF statement when creating a measure.   Suppose I have the follow...
Need Help
Tips and Tricks
Tutorial Requests
  • mlsx4's avatar
    mlsx4
    3 years ago

    Hi Anonymous 

     

    Try this (modify values as you need):

     

     

    Measure = 
var _column= SELECTEDVALUE(Sheet1[Obligation Type])
var _suma= SUM(Sheet1[Set-off / payment amount])
return
    SWITCH(_column,
    "VAT", (_suma)*0.25,
    "WHT 23",(_suma)*0.3,
    "WHT 21", (_suma)*0.21,
    "WHT 4(2)", (_suma)*0.x,
    _suma)

     

     

mlsx4's avatar
mlsx4
Memorable Member
3 years ago

Hi Anonymous 

 

I would recommend you to use a SWITCH statement:

SWITCH(Sheet1[Obligation Type],
"VAT", (Sheet1[Set-off / payment amount]) * 0.25,
"WHT 23", (Sheet1[Set-off / payment amount]) * 0.3,

... and so on )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi mlsx4 !

     

    Thank you for your response!

     

    Questions:

    1) May I know the difference between SWITCH and IF statement?

    2) Are there any limits on how many condition I can put for the SWITCH statement?

    3) Can I put a numeric condition for SWITCH statement? Something like IF A>B or X<Y.

    • mlsx4's avatar
      mlsx4
      Memorable Member
      3 years ago

      Hi Anonymous 

       

      1) IF is better to compare just true or false results, SWITCH is better for a variable that takes multiple values. It also makes the formula visually clearer.
      2) No, there's no limit

      3) Yes, you can include condition inside a SWITCH. The structure is:

      SWITCH (Value,
      ValueObtained, <whatever expresion>,

      ValueObtained1, <whatever expresion>...

      [Default value if none of the selected], <what you want to do> -->it is not mandatory to write this

      )

      I hope I was able to clarify your doubts. Anyway, don't hesitate to ask me again if it is not the case

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thank you for your responses!

         

        Understood on all of your points above.

         

        By the way, I also sent an additional reply where I tried your code but it did not work.

         

        Anything that I can revise to make it work?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    In addition to my reply above,

     

    I tried putting in the code you shared. However it seems that the code is not working.

    It seems like the formula only work only for measure fields?

     

    Here is my list of fields for your reference:

     

    • mlsx4's avatar
      mlsx4
      Memorable Member
      3 years ago
      Hi again, Anonymous 
      Can you try to use something like:

      var _column= SELECTEDVALUE(Sheet1[Obligation Type])
      return
          SWITCH(_column,...)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi!

         

        Can use something like this?

         

        Switch1 = SWITCH(SELECTEDVALUE(Sheet1[Obligation type]),
        "VAT",SELECTEDVALUE(Sheet1[Set-off / payment amount])*0.2,
        "WHT 21",SELECTEDVALUE(Sheet1[Set-off / payment amount])*0.35,
        "WHT 23",SELECTEDVALUE(Sheet1[Set-off / payment amount])*0.4,
        "WHT 4(2)",SELECTEDVALUE(Sheet1[Set-off / payment amount])*0.1)