cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
estewart New Member
New Member

Lookup values (or other method?) from same table

I am trying to create a new column that assigns trend "start date" to trend "data value". See table below. Columns in gray are what I have currently. Col A has two variable types "Trend_x" which is the data value and Trend_x_Start" which is the start date. YearQuarter represents different quarterly forecasts. Value contains either the data value or the trend start date (e.g. YYYY.Q format).

 

Trend start date is not consistent across all (a) Trend_x and (b) YearQuarter combinations.

 

I am trying to create the column in green at the query or table level using DAX. So far I haven't been able to get it to work.

 

Any help is appreciated. Thank you.

 

Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Lookup values (or other method?) from same table

Hi @estewart ,

 

Sorry for the delay.

 

For your sceanrio, you could try the steps below.

 

1. Go to Query editor and duplicate the original table and filter rows with Variable.

 

filter rows.PNG

 

2. Apply and Close , create a calcualted column in table 2 with the formula below.

 

 

Column = IF('Table2'[Variable] = "Trend_1_Start",1,2)

3.  Create two calculated columns in Table.

 

Column = IF('Table1'[Variable] in {"Trend_1","Trend_1_Start"},1,2)
Column 2 =
LOOKUPVALUE (
    'Table2'[Value],
    'Table2'[YearQuarter], 'Table1'[YearQuarter],
    Table2[Column], 'Table1'[Column]
)

Here is the output.

 

Capture.PNG

 

More details please refer to my attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: Lookup values (or other method?) from same table

Hi @estewart ,

 

Sorry for the delay.

 

For your sceanrio, you could try the steps below.

 

1. Go to Query editor and duplicate the original table and filter rows with Variable.

 

filter rows.PNG

 

2. Apply and Close , create a calcualted column in table 2 with the formula below.

 

 

Column = IF('Table2'[Variable] = "Trend_1_Start",1,2)

3.  Create two calculated columns in Table.

 

Column = IF('Table1'[Variable] in {"Trend_1","Trend_1_Start"},1,2)
Column 2 =
LOOKUPVALUE (
    'Table2'[Value],
    'Table2'[YearQuarter], 'Table1'[YearQuarter],
    Table2[Column], 'Table1'[Column]
)

Here is the output.

 

Capture.PNG

 

More details please refer to my attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.