Forum Discussion
Solved
Remove Row Context in Calculated Column
Thanks Advanced,
I would like to create an calculated column VAR function.
Input Table :
| Jobcode | Queue | M1 | M2 |
| 600234 | Q1 | 23 | 99 |
| 600234 | Q2 | 43 | 87 |
| 600234 | Q3 | 23 | 67 |
| 602897 | Q1 | 54 | 76 |
| 602897 | Q2 | 65 | 87 |
| 602897 | Q3 | 76 | 89 |
| 602897 | Q4 | 87 | 90 |
| 602897 | Q5 | 23 | 98 |
| 889977 | Q2 | 44 | 77 |
| 889977 | Q1 | 33 | 49 |
| 889977 | Q3 | 22 | 62 |
| 776655 | Q2 | 77 | 79 |
| 776655 | Q1 | 64 | 81 |
| 776655 | Q3 | 59 | 39 |
| 776655 | Q4 | 90 | 63 |
| 776655 | Q5 | 72 | 58 |
| 776655 | Q6 | 38 | 35 |
| 776655 | Q7 | 61 | 86 |
Expected Result :
| Jobcode | Queue | M1 | M2 | Result |
| 600234 | Q1 | 23 | 99 | 23 |
| 600234 | Q2 | 43 | 87 | 43 |
| 600234 | Q3 | 23 | 67 | 22 |
| 602897 | Q1 | 54 | 76 | 23 |
| 602897 | Q2 | 65 | 87 | 43 |
| 602897 | Q3 | 76 | 89 | 22 |
| 602897 | Q4 | 87 | 90 | 87 |
| 602897 | Q5 | 23 | 98 | 23 |
| 889977 | Q2 | 44 | 77 | 43 |
| 889977 | Q1 | 33 | 49 | 23 |
| 889977 | Q3 | 22 | 62 | 22 |
| 776655 | Q2 | 77 | 79 | 43 |
| 776655 | Q1 | 64 | 81 | 23 |
| 776655 | Q3 | 59 | 39 | 22 |
| 776655 | Q4 | 90 | 63 | 87 |
| 776655 | Q5 | 72 | 58 | 23 |
| 776655 | Q6 | 38 | 35 | 38 |
| 776655 | Q7 | 61 | 86 | 61 |
Logic :
We have to get the minimum value in M1 with groupby Queue.
My badluck , i have to implement this in Visual Studio 2013 so i can't use the VAR DAX function. and it should in calculated Column
Greg_Deckler Zubair_MuhammadSean
You can use
Result = MINX ( FILTER ( Table1, [Queue] = EARLIER ( [Queue] ) && [Jobcode] > EARLIER ( [Jobcode] ) ), [M1] )or the ALLEXCEPT version
Result2 = CALCULATE ( MIN ( Table1[M1] ), FILTER ( ALLEXCEPT ( Table1, Table1[Queue] ), [Jobcode] > EARLIER ( [Jobcode] ) ) )
4 Replies
Hi,
Try this column
Column = MINX ( TOPN ( 1, FILTER ( Table1, [Queue] = EARLIER ( [Queue] ) ), [M1], ASC ), [M1] )or this one
Column 2 = CALCULATE ( MIN ( Table1[M1] ), ALLEXCEPT ( Table1, Table1[Queue] ) )
Zubair_Muhammad Thanks for your quick response on this thread.
Have to say sorry here, because i have missed one more logic in my requirement
Have to add one more condition
1. jobcode should be greaer than current jobcode.
Jobcode Queue M1 M2 Result 600234 Q1 23 99 33 600234 Q2 43 87 44 600234 Q3 23 67 22 602897 Q1 54 76 33 602897 Q2 65 87 44 602897 Q3 76 89 22 602897 Q4 87 90 90 602897 Q5 23 98 72 776655 Q2 77 79 44 776655 Q1 64 81 33 776655 Q3 59 39 22 776655 Q4 90 63 776655 Q5 72 58 776655 Q6 38 35 776655 Q7 61 86 889977 Q2 44 77 889977 Q1 33 49 889977 Q3 22 62 You can use
Result = MINX ( FILTER ( Table1, [Queue] = EARLIER ( [Queue] ) && [Jobcode] > EARLIER ( [Jobcode] ) ), [M1] )or the ALLEXCEPT version
Result2 = CALCULATE ( MIN ( Table1[M1] ), FILTER ( ALLEXCEPT ( Table1, Table1[Queue] ), [Jobcode] > EARLIER ( [Jobcode] ) ) )
