Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Reference a Row In Another Column

How do I reference data in a row of a column and place it in a new column all on the same table?   Column A             Column B Data 1                    Data 3 Data 2                    Data 3 ...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    Based on your description and expected output, one thing I could not understand is you have the same value for Data 1,2,3 but how could column B only show values in the first two rows.

    If this is your final expected result, maybe you need an index column, create an index column in power query editor, close and apply it, create this calculated column:

    Column B2 = 
IF(
    [Index] <= 2,
    CALCULATE(
        MAX('Table'[Column A]),
        FILTER(
            ALL('Table'),
            'Table'[Rank] = MAX('Table'[Rank])
        )
    ),
    BLANK()
)

    If both Data 1 and Data 2 rows have Data 3, create this calculated column without index column:

    Column B = 
IF(
    [Rank] <> MAX('Table'[Rank]),
    CALCULATE(
        MAX('Table'[Column A]),
        FILTER(
            ALL('Table'),
            'Table'[Rank] = MAX('Table'[Rank])
        )
    ),
    BLANK()
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I used the RANKX function to get the Rank column, so "Column A" and "Rank" is my current state. I want a new column(Column B), where I only have "Data 3" from "Column A". I'm have trouble writing DAX that will get me to the desired state, "Column B". Any help will be much appreciated.

 

Column A    Rank    Column B

Data 1          1          Data 3

Data 2          2          Data 3

Data 3          3

Data 1          1

Data 2          2

Data 3          3

 

Hope this gives some more clarity.

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

Based on your description and expected output, one thing I could not understand is you have the same value for Data 1,2,3 but how could column B only show values in the first two rows.

If this is your final expected result, maybe you need an index column, create an index column in power query editor, close and apply it, create this calculated column:

Column B2 = 
IF(
    [Index] <= 2,
    CALCULATE(
        MAX('Table'[Column A]),
        FILTER(
            ALL('Table'),
            'Table'[Rank] = MAX('Table'[Rank])
        )
    ),
    BLANK()
)

If both Data 1 and Data 2 rows have Data 3, create this calculated column without index column:

Column B = 
IF(
    [Rank] <> MAX('Table'[Rank]),
    CALCULATE(
        MAX('Table'[Column A]),
        FILTER(
            ALL('Table'),
            'Table'[Rank] = MAX('Table'[Rank])
        )
    ),
    BLANK()
)

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

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