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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - Depends, you have to have some way to reference it like an Index or something or perhaps it is the MIN or MAX of that column. 

 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

 

  • 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.