Forum Discussion
DAX measure to return minimum absolute value while maintaining sign
Hi All,
I have hit a roadblock with a measure I am trying to create.
I have the following table:
Order | Work Center | Actual Run Time | Expected Run Time |
| 1 | A | 30 | 20 |
| 2 | A | 20 | 20 |
| 3 | B | 15 | 20 |
| 4 | B | 10 | 20 |
I am calculating the time deviation as [Actual Run Time] - [Expected Run Time].
I am trying to create a measure that calculates the smallest absolute time deviation while maintaining the sign.
So for Work Center A the two time deviations are 10 and 0. I want the measure to return 0.
For Work Center B the two deviations are -5 and -10. I want the measure to return -5.
The measure will be displayed in a table visual like this:
| Work Center | Min Run Time Deviation |
| A | 0 |
| B | -5 |
I have created the following measure that calculates the minimum absolute value but I can't figure out how to keep the sign if the time deviation is negative.
Well, here is one way, but I don't like it because if you have a 5 and a -5 together you will end up with the -5. Not sure if that is OK.
PBIX attached.
2 Replies
- Greg_Deckler
Community Champion
Well, here is one way, but I don't like it because if you have a 5 and a -5 together you will end up with the -5. Not sure if that is OK.
PBIX attached.
Hi Greg,
The solution you provided works for my use case. The chances of having identical positive and negative deviations is almost 0 as the actual times are recorded in milliseconds. Thanks for the help!
