Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |