Forum Discussion
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
Data 3 Data 3
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
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.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.
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
You will need to be a bit clearer in your request, and please use tables to put in data. We cannot use text in the post as samples as it takes time to get rid of the line feeds and spaces. See link at bottom.
But, you can reference other columns. In Power Query, you can add a new custom column with the following formula:
List.Max(Source[Column A])Source is the step above the one you are in, so in my case, I only had a Source step. It could be #"Changed Step" or whatever. So it looks at the table called "Source" (power query steps are really tables, or lists or some other object) and then [Column A] and gets the max value, so now Data 3 is in all rows of the new column.
In DAX, the following measure will add a new column to a table visual.
New Column for Table Visual = CALCULATE( MAX('Data Table'[Column A]), REMOVEFILTERS('Data Table'[Column A]) )So you get this:
I'm not going to show you how to do a calculated column, because you shouldn't use those. 😉
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures and Calculated ColumnsHow to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumAnonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Looking at column B you can get like
Column B = max([Column A])
Appreciate your Kudos.
