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 following dataset:

I wanted to make a measure which looks similar like this:

Group 1 = SELECTEDVALUE(Sheet1[Set-off / payment amount]) * 0.25
 
But I wanted to make it so that if the Obligation Type is "VAT", then it becomes * 0.25, else if the Obligation Type is "WHT 23", then it becomes * 0.3, else if the Obligation Type is "WHT x", then it becomes * 0.x, and so on.
 
Is it possible to create such measure in PowerBI?
 
Best regards,
Bona
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)

     

     

10 Replies

  • 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

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