Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
agustin_garcia
Frequent Visitor

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])

 

1 ACCEPTED SOLUTION

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_nameWeekShift
emp1101st
emp1112nd
emp2101st
emp2111st

View solution in original post

4 REPLIES 4
agustin_garcia
Frequent Visitor

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

Descweek_start_dateemployee_nameCalculated_column
wer12-Oct-15emp11st
wert15-Sep-15emp22nd
wer12-Feb-15emp11st

 

From_table

employee_name12-Feb-1515-Sep-1512-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):

https://goo.gl/ada3b1

 

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_nameWeekShift
emp1101st
emp1112nd
emp2101st
emp2111st

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.