Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
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.
More details please refer to my attachment.
Best Regards,
Cherry
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.
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.
More details please refer to my attachment.
Best Regards,
Cherry
User | Count |
---|---|
94 | |
85 | |
78 | |
68 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |