Forum Discussion
Using LOOKUPVALUE with dinamically created column names
Hello,
I need to use LOOKUPVALUE to gather data from a second table, where result_ from_column column's name is based on data existing in current table. Hence I need to pass a column name that is dinamically parsed.
Code extract below shows a working example when I use a fixed column name (result_from_col), but if I build up the column name (("result_from_table[" & WEEKNUM(current_table[week_start_date], 2) & "]") and pass it as argument, I receive an errror like "Function LOOKUPVALUE expects a column reference as argument number 1"
Somthing to highlight here is that depending on the content of "current_table[week_start_date]", my from data may be in different column from the queried table result_from_table
If this is not possible, with LOOKUPVALUE, what other approach could I use to achieve this?
Shift = LOOKUPVALUE(result_from_table[result_from_col], result_from_table[result_from_common_key], query_current_table[query_current_table_common_key])
Shift = LOOKUPVALUE("result_from_table[" & WEEKNUM(current_table[week_start_date], 2) & "]", restult_from_table[result_from_common_key], current_table[query_current_table_common_key])
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
4 Replies
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
