Forum Discussion

loodle's avatar
loodle
Frequent Visitor
4 years ago
Solved

Create New Column From Duplicates

This is probably simple:

Data is this:

IDSTATEFRUIT
1OHAPPLE
2OHAPPLE
3PAAPPLE 
3PAORANGE
4OHAPPLE
5OHAPPLE
5OHORANGE
5OHLIME
6OHAPPLE

Like to create new columns to look like this:

IDSTATEFRUITFRUIT2FRUIT3
1OHAPPLE  
2OHAPPLE  
3PAAPPLE ORANGE 
3PAORANGEAPPLE 
4OHAPPLE  
5OHAPPLEORANGELIME
5OHORANGELIMEAPPLE
5OHLIMEAPPLE ORANGE
6OHAPPLE  

 

Not sure if LOOKUP would work here, needs to look at rows where ID is duplicated and get value from there.

 

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.

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity 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
        Icon for Community Support rankCommunity 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.