Matrix Visual - creating a measure with IF statement

[Anonymous]

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

[mlsx4]

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]

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]

Hi again, @Anonymous 
Can you try to use something like:

var _column= SELECTEDVALUE(Sheet1[Obligation Type])

return

    SWITCH(_column,...)

[Anonymous]

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)

[mlsx4]

Mmmm... it may not work... 

Can you provide a sample of data? I cannot figure out how can you calculate it without knowing the structure

[Anonymous]

I uploaded the dummy file to dropbox for your reference: https://www.dropbox.com/scl/fi/5jngy5sj8wsd86mqcdsrj/Dummy-Data-for-Report.xlsx?rlkey=wlztk97eexhr2i...

[mlsx4 · Accepted solution]

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)

[Anonymous]

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]

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]

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?