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.] 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?

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!

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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'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!