Forum Discussion

PhoenixBird66's avatar
PhoenixBird66
Helper III
6 years ago
Solved

DAX help please - slight amendment

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.] whe...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

     

PhoenixBird66's avatar
PhoenixBird66
Helper III
6 years ago

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's avatar
v-kelly-msft
Community Support
6 years ago

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!