cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Remove Row Context in Calculated Column

Thanks Advanced, 

 

I would like to create an calculated column  VAR function.

 

Input Table :

JobcodeQueueM1M2
600234Q12399
600234Q24387
600234Q32367
602897Q15476
602897Q26587
602897Q37689
602897Q48790
602897Q52398
889977Q24477
889977Q13349
889977Q32262
776655Q27779
776655Q16481
776655Q35939
776655Q49063
776655Q57258
776655Q63835
776655Q76186

 

Expected Result :

JobcodeQueueM1M2Result
600234Q1239923
600234Q2438743
600234Q3236722
602897Q1547623
602897Q2658743
602897Q3768922
602897Q4879087
602897Q5239823
889977Q2447743
889977Q1334923
889977Q3226222
776655Q2777943
776655Q1648123
776655Q3593922
776655Q4906387
776655Q5725823
776655Q6383538
776655Q7618661

 

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Remove Row Context in Calculated Column

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

 baskar2.png

4 REPLIES 4
Super User
Super User

Re: Remove Row Context in Calculated Column

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

 

 

baskar1.png

 

Super User
Super User

Re: Remove Row Context in Calculated Column

@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.

 

JobcodeQueueM1M2Result
600234Q1239933
600234Q2438744
600234Q3236722
602897Q1547633
602897Q2658744
602897Q3768922
602897Q4879090
602897Q5239872
776655Q2777944
776655Q1648133
776655Q3593922
776655Q49063 
776655Q57258 
776655Q63835 
776655Q76186 
889977Q24477 
889977Q13349 
889977Q32262 

 

 

 

Super User
Super User

Re: Remove Row Context in Calculated Column

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

 baskar2.png

Super User
Super User

Re: Remove Row Context in Calculated Column

@Zubair_Muhammad Its working fine , thanks a lot