Forum Discussion

agustin_garcia's avatar
agustin_garcia
Frequent Visitor
10 years ago
Solved

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 di...
Need Help
  • agustin_garcia's avatar
    agustin_garcia
    10 years ago

    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
agustin_garcia's avatar
agustin_garcia
Frequent Visitor
10 years ago

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

agustin_garcia's avatar
agustin_garcia
Frequent Visitor
10 years ago

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