cancel
Showing results for
Did you mean:
Super User

## Remove Row Context in Calculated Column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Remove Row Context in Calculated Column

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 4
Super User

## Re: Remove Row Context in Calculated Column

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] ) )
```

Super User

## Re: Remove Row Context in Calculated Column

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

Super User

## Re: Remove Row Context in Calculated Column

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] )
)
)
```

Super User

## Re: Remove Row Context in Calculated Column

@Zubair_Muhammad Its working fine , thanks a lot