Forum Discussion
Comparison using IF for multiple conditions
Hello,
I have the following table loaded in Power Query and i have a measure which compares the "Calculated value" to the "Input value" for each row and then i used conditional formatting in the Calculated value column.
I am trying to do the following:
If status = 0, compare the calculated and input value. If equal, then set to 0, otherwise set to 1.
If status = 1, compare the calculated and input value. If equal, then set to 2, otherwise set to 3.
Here is my current measure but it only checks if the Calculated vs. Input values are same. Any help is much appreciated!
Value Check =
IF (
ROUND ( [Calc value Measure], 0 )
<> ROUND ( [Input value Measure], 0 ),
1,
0
)
| Document ID | Calculated value | Input value | Status |
| 3998 | 2439.17 | 2439.17 | 0 |
| 4050 | 1176.56 | 1176.56 | 1 |
| 4050 | 791024.45 | 791024.45 | 0 |
| 4060 | 11810.25 | 1810 | 0 |
| 4139 | 12352.03 | 12352.03 | 1 |
| 4145 | 81045.86 | 81045.86 | 0 |
| 4149 | 365.95 | 545.95 | 1 |
| 4157 | 4509.88 | 4513.88 | 0 |
| 4160 | 52011 | 52011 | 0 |
| 4160 | 2922.4 | 542.4 | 0 |
| 4164 | 9592.56 | 9592.56 | 0 |
| 4166 | 32471.67 | 87441.67 | 0 |
| 4187 | 6793.62 | 6793.62 | 0 |
| 4187 | 33938.1 | 33938.1 | 0 |
| 4187 | 4781.14 | 4390 | 1 |
| 4187 | 14124 | 13538 | 1 |
| 4248 | 104310 | 154310 | 0 |
| 4314 | 4598.01 | 4598.01 | 1 |
| 4474 | 78597 | 78597 | 0 |
| 4481 | 310904.73 | 94904.73 | 0 |
| 4487 | 372359.04 | 372359.04 | 0 |
| 4495 | 1144 | 1144 | 0 |
| 4685 | 734125.54 | 734125.54 | 0 |
| 4688 | 4073682 | 4073682 | 0 |
| 4691 | 145683 | 175683 | 1 |
| 4813 | 180000 | 84100 | 0 |
| 4832 | 437233 | 437233 | 0 |
| 4997 | 1636831 | 1636831 | 0 |
This measures should do what you're looking for.
# Value Check = VAR thisStatus = SELECTEDVALUE('Table'[Status]) VAR thisCalculatedValue = SELECTEDVALUE('Table'[Calculated value]) VAR thisInputValue = SELECTEDVALUE('Table'[Input value]) RETURN SWITCH( TRUE(), thisStatus = 0, IF(thisCalculatedValue = thisInputValue, 0, 1), thisStatus = 1, IF(thisCalculatedValue = thisInputValue, 2, 3) )If this solves your problem, please accept this as the solution.
1 Reply
This measures should do what you're looking for.
# Value Check = VAR thisStatus = SELECTEDVALUE('Table'[Status]) VAR thisCalculatedValue = SELECTEDVALUE('Table'[Calculated value]) VAR thisInputValue = SELECTEDVALUE('Table'[Input value]) RETURN SWITCH( TRUE(), thisStatus = 0, IF(thisCalculatedValue = thisInputValue, 0, 1), thisStatus = 1, IF(thisCalculatedValue = thisInputValue, 2, 3) )If this solves your problem, please accept this as the solution.