Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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_Muhammad@Sean
Solved! Go to Solution.
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] ) ) )
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] ) ) )
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |