topic Re: Calculate status in DAX Commands and Tips

Calculate status

jitpbi — Thu, 13 Aug 2020 17:59:50 GMT

Hi,

I need help to get the status of a field based on few conditions. The below is the sample data where "New Stats" is the required field:

Output1	Output2	Status	New Status
100	150	Good	Good
0	0	Good	Error
0	100	Good	Error
50	50	Normal	Normal
120	130	Normal	Normal

Status field has only 2 values Good and Normal but i need to calculate 3 values in Status field based on the below conditions:

If Output1 < 5 and Output2 > 100 then "Error"

Output1 = 0 and Output2 = 0 then "Error"

other then above scenarios my new status field should pick the same value of the Status field.

Thanks

Re: Calculate status

AntrikshSharma — Thu, 13 Aug 2020 18:15:26 GMT

jitpbi Try this:

New Status = VAR FirstOutput = Table[Output1] VAR SecondOutput = Table[Output2] VAR CurrentStatus = Table[Status] VAR Result = IF ( OR ( AND ( FirstOutput < 5, SecondOutput > 100 ), AND ( FirstOutput = 0, SecondOutput = 0 ) ), "Error", CurrentStatus ) RETURN Result

Re: Calculate status

amitchandak — Thu, 13 Aug 2020 18:44:32 GMT

jitpbi , You can create a new column like

New Status =
Switch(true(),
[Output1] < 5 && [Output2] >= 100 , "Error",
[Output1] =0 && [Output2] = 0 , "Error",
"Normal"
)

Re: Calculate status

Anonymous — Fri, 14 Aug 2020 10:10:41 GMT

Based on your conditions, the output in the 3rd row of your table is wrong. It should be Good, not Error.

Re: Calculate status

jitpbi — Fri, 14 Aug 2020 10:28:51 GMT

Anonymous Actually, the status is determined based on the values not on the pattern. Thanks

Re: Calculate status

Anonymous — Fri, 14 Aug 2020 11:15:19 GMT

Here's your rules:

If Output1 < 5 and Output2 > 100 then "Error"
Output1 = 0 and Output2 = 0 then "Error"
other then above scenarios my new status field should pick the same value of the Status field.

Now apply those to the table you've posted and you'll see I'm right. I don't even understand what you mean by saying "the status in determined based on the values not on the pattern." What pattern are you talking about? The only thing that is used to derive the values is the rules above.... No pattern in sight.