Forum Discussion
Using LOOKUPVALUE with dinamically created column names
Hi again,
Just to confirm that I was able to understand your porposal (and your comment regargin unpivoting the table)
I did created a temp unpivoted table via Power Query then the solution was easy to implement, which I achieved doing something like:
Shift = LOOKUPVALUE (
unpivot_from_table[Shift],
unpivot_from_table[Resource Name], current_table[Time Entry Resource Name],
unpivot_from_table[Week], WEEKNUM(current_table[week_start_date])
)The solution basically makes use of a LEFT JOIN in SQL implemented in DAX via the LOOKUPVALUE function as depicted in SQL BI's From SQL to DAX: Joining Tables article.
The unpivoted table (by creating a new tab in my Excel file and applying power query's Unpivot function, looks like:
unpivot_from_table
employee_name Week Shift emp1 10 1st emp1 11 2nd emp2 10 1st emp2 11 1st
Perhaps it helps if I provide the sample table below, basically what I'm trying is to populate a calculated column with cross reference from_table, something that in Excel is achieved with a =INDEX(, MATCH(, ,0), MATCH(,,0) ) formula
Thanks in advance for your comments
Current table
| Desc | week_start_date | employee_name | Calculated_column |
| wer | 12-Oct-15 | emp1 | 1st |
| wert | 15-Sep-15 | emp2 | 2nd |
| wer | 12-Feb-15 | emp1 | 1st |
From_table
| employee_name | … | 12-Feb-15 | … | 15-Sep-15 | … | 12-Oct-15 |
| emp1 | 1st | 2nd | 1st | |||
| emp2 | 2nd | 2nd | 1st |
<hi agustin_garcia,
I know my reply does not answer your question directly, but I think you don't have a DAX problem, but an ETL problem. The fact that you are dealing with changing headers is caused by the fact, that you are working with pivoted tables in your data model. My advice is to unpivot the "From_table", before going on with DAX. I used Power Query for that task and linked the final table into the data model.
Please see my example file following this link (I used Excel 2013):
Regards,
Lars
Thanks Lars,
You are right, what I'm trying to do is to understand how to transform my Excel (or your Power Query) ETL scenario into DAX (via Power BI)
Just to clarify, the table structure I'm showing is the plain structure from both an Oracle DB extract and an Excel table, I'm not using any pivoting nor I have done any tranformation yet.
I should've mentioned that I'm a novice on DAX as well, maybe that'd help to understand my question :-)
Thanks again, I really appreciate your effort put into this, since it's helpful.
Coming to my "novice" comment, if anyone has any suggestions on a more DAX focused approach it'd be welcomed
Hi again,
Just to confirm that I was able to understand your porposal (and your comment regargin unpivoting the table)
I did created a temp unpivoted table via Power Query then the solution was easy to implement, which I achieved doing something like:
Shift = LOOKUPVALUE (
unpivot_from_table[Shift],
unpivot_from_table[Resource Name], current_table[Time Entry Resource Name],
unpivot_from_table[Week], WEEKNUM(current_table[week_start_date])
)The solution basically makes use of a LEFT JOIN in SQL implemented in DAX via the LOOKUPVALUE function as depicted in SQL BI's From SQL to DAX: Joining Tables article.
The unpivoted table (by creating a new tab in my Excel file and applying power query's Unpivot function, looks like:
unpivot_from_table
employee_name Week Shift emp1 10 1st emp1 11 2nd emp2 10 1st emp2 11 1st
