Forum Discussion

loodle's avatar
loodle
Frequent Visitor
4 years ago
Solved

Create New Column From Duplicates

This is probably simple: Data is this: ID STATE FRUIT 1 OH APPLE 2 OH APPLE 3 PA APPLE  3 PA ORANGE 4 OH APPLE 5 OH APPLE 5 OH ORANGE 5 OH LIME 6 OH...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi loodle 

    I find a more convenient way,  it just need to change the variable _n in the dax expression (see below)

    test1 =
VAR _n = 1
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _IndexStart =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _IndexEnd =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _searchIndex =
    IF (
        'Table'[Index] + _n > _IndexEnd,
        'Table'[Index] + _n - _count,
        'Table'[Index] + _n
    )
RETURN
    IF (
        _count > _n,
        CALCULATE (
            MAX ( 'Table'[FRUIT] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Index] = _searchIndex )
        ),
        BLANK ()
    )

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi loodle 

You can try this, create index column first, then create the calculated column below

Column =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _Index1 =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] ),
            'Table'[Index] > EARLIER ( 'Table'[Index] )
        )
    )
VAR _Index2 =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _searchIndex =
    IF ( ISBLANK ( _Index1 ), _Index2, _Index1 )
RETURN
    IF (
        _count > 1,
        CALCULATE (
            MAX ( 'Table'[FRUIT] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Index] = _searchIndex )
        ),
        BLANK ()
    )

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • loodle's avatar
    loodle
    Frequent Visitor
    4 years ago

    Thanks for the help. This sort of worked, but did not create a third column where there is an ID that has three fruits associated with it (Fruit3 in my example above).

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi loodle 

      I find a more convenient way,  it just need to change the variable _n in the dax expression (see below)

      test1 =
VAR _n = 1
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _IndexStart =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _IndexEnd =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATE] )
    )
VAR _searchIndex =
    IF (
        'Table'[Index] + _n > _IndexEnd,
        'Table'[Index] + _n - _count,
        'Table'[Index] + _n
    )
RETURN
    IF (
        _count > _n,
        CALCULATE (
            MAX ( 'Table'[FRUIT] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Index] = _searchIndex )
        ),
        BLANK ()
    )

      Best Regards,

      Community Support Team _Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.