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!

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
Can you post that data as text?