DAX help please - slight amendment

[PhoenixBird66]

I've added a new column to my data set called [LineNo.New] which looks for any row where the [Standard ServiceCode] is blank, and it takes the [Line No.] from the first row on that [Document No.] where [Standard service code] is not blank.

Here is the DAX I used to get this :

LineNo.New = IF(Table[Standard Service Code]=BLANK(), CALCULATE(MIN(Table[Line No.]), ALLEXCEPT(Table, Table[Document No.])), Table[Line No.])

However, I now need to amend this to cater for situations where there is more than 1 [Standard Service Code] on each [Document No.] .

So on row 8, [Line No.New] should say 160000 rather than 50000.

Any idea how I can acheieve this?

[amitchandak]

Why not 7000 in row 4.

Mark me @

If it can be 7000, Try both as new columns

Last line no = maxx(filter(table, table[document_no]=earlier(table[document_no]) && table[line_no]<earlier(table[line_no])),Table[line_no])

line no new= if(isblank(table[line_no]),table[Last line no],table[line_no])

[PhoenixBird66]

Thanks @amitchandak 

I Tried both of these but sadly it' not quite what I need. See the last 2 columns in the picture below.

On row 4 it as corretcly given the [Last line no] value as 7000, because there is no [Standard Service Code] on that row. However I dont think it will work in a situation where there are 2 or more rows one after another where there is no Standard Service Code.

Also the [line no new] column doesnt given the resuts I need either. On Row 4, I want the new value to read 7000 (not 8000), as that is the line number of the earlier row where there is a Standard Service Code.

[v-kelly-msft · Accepted solution]

Hi @PhoenixBird66 ,

First create an index column from 1 in query editor.

Then create a calculated column as below:

LineNo.New 2 =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Document No] = EARLIER ( 'Table'[Document No] )
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                && 'Table'[Standard Service Code] <> BLANK ()
        )
    )
RETURN
    IF (
        'Table'[Standard Service Code] = BLANK (),
        CALCULATE ( SUM ( 'Table'[Line No.] ), FILTER ( 'Table', 'Table'[Index] = _a ) ),
        'Table'[Line No.]
    )

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Can you post that data as text?