Remove Row Context in Calculated Column

[Baskar]

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 

[Zubair_Muhammad]

@Baskar

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

[Baskar]

@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

[Zubair_Muhammad · Accepted solution]

@Baskar

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

 

[Baskar]

@Zubair_Muhammad Its working fine , thanks a lot