Create a custom column with dynamic lookup in related table

[Anonymous]

I've tried several approaches, and searched the web in more than a dozen different ways trying to figure this out, but I'm not having any luck. Hoping someone will be able to provide some guidance on how to do this, or if there is a better way.

Table 1 contains a variety of different columns, one of which we will call 'category', and another which is 'region'. Table 2 has a single column with unique values only for 'category', but each region is represented by its own column, and the corresponding cell in this second table indicates the responsible person for that category. Sometimes this value will be blank.

There are actually a few more columns in Table 1, which is the primary data table, but I am providing only a simplified example below. Table 2 is coming from another source and I would rather not try to recreate and maintain a separate copy of this information, which changes randomly.

Table 1

Category           Region           

-----------          ---------         

CatValue1          Southwest   

CatValue1          Northeast

CatValue2          Southwest

CatValue2          Northeast

Table 2

Category     Southwest          Northeast

----------     ------------         -------------

CatValue1    Bob Johnson      Betty Jackson

CatValue2    Jim Morris          

Power BI Desktop has already created a many-to-one relationship for the category. 

What I'm trying to do is to create a dynamic column in Table 1 that shows the person responsible for that category and region. The way I have been trying to do this is to try and figure out how to dynamically reference the value of the region cell in Table 1 to provide the column reference in a Related query to return the intersecting value from Table 2, as shown in the example below.

Table 1

Category           Region           Responsible (calculated column)

-----------          ---------         -------------

CatValue1          Southwest      Bob Johnson

CatValue1          Northeast       Betty Jackson

CatValue2          Southwest      Jim Morris

CatValue2          Northeast       

Unfortunately my knowledge of DAX is limited, though I can kind of get around. My knowledge of M is complete beginner level, at best, so I've no idea where to even begin trying this in the advanced editor. If there's an easier way than creating a dynamic formula, I'm all ears.

Thanks in advance for any help.

Chris

[Anonymous]

Hi Chris,

You can do it in two ways,

First, you have two tables and you can write sql query by using joins and bring the columns whatever you want.

Ex. SELECT T1.Category, T1.Region, T2.SouthWest, T2.NorthEast

      FROM Table1 T1 WITH(NOLOCK)

      INNER JOIN Table2 T2 WITH(NOLOCK)         /* Here you can user other joins as well (left outer join, Right outer join,etc..)

      ON T1.Category = T2.Category

Second, You have Table1 which contains Category and Region and Table2 Consists of Category, SouthWest and NorthEast.

So, both the tables are having key columns to connect with each other. That is "Category".

Relate the above two tables with the Cateory column and Drag the columns whatever you want to display it in visual, you can use the columns from table1 and table2.

Ex. Your table/matrix visual will have Category,Region,Southwest, Norhtwest

This will not throw an error.

Regards,

Pradeep

[Anonymous · Accepted solution]

Apologies as, looking back, it's clear that my explanation was not complete enough, which may have created some confusion. In this instance, I do not have access to the SQL tables. The data was provided as exported Excel files from two completely different and disconnected data sources. When I refer to 'Table' in this case, I am referring to the individual data sets that have each been formatted as a table on a separate worksheet in a newly combined workbook. I am connecting Power BI desktop to this new combined source Excel file as two data sources, which then imports the data two Power BI data tables. 

Unfortunately there are a number of challenges that limit my options.

1. The person who will be maintaining the feeder data in the future is a non-technical senior manager with limited time, so the process must be simple, straight forward, and relatively quick to execute. 
2. The two source teams are currently rejecting requests for direct access to the data in any form, and will only provide an extract.
3. I have a very limited amount of time to build out this initial set of reports, so I don't have time to build something that automates everything the way I would like to in an elegant and efficient manner at this stage.

Because of these limitations, I'm trying to set up a simple process that will allow the senior manager to just copy and paste from the source files into the feeder file. The feeder will be hosted in the Power BI service, and the reports will automatically refresh so that he, and the other senior and C-levels who will consume the content, can carry on without further involvement from me...at least until such time as they want to build this into a proper tool.

I know that, in theory, I could use Power Query to just combine the data during the data import/refresh process, but as I mentioned, I lack the requisite knowledge to even figure out what to search. Learning Power Query is on my ever growing list of things I plan to learn as soon as I have time, but right now I'm limited to whatever I might be able to pull off with DAX and/or the one-click automated transformations available graphically in the Query Editor.

Fortunately, I have been able to sort of find a way to make things work, though not necessarily elegant or ideal. For the purposes of anyone else who might stumble across this thread in the future, I used the following process:

• In Power BI, open the Query Editor
• Select the second table
• Select the first column, which contains the unique row level labels
• Switch to the Transform section of the ribbon
• In the 'Any Column' section, click the drop-down arrow next to 'Unpivot Columns', and select 'Unpivot Other Columns'

Doing this changes the values and columns around so that my row labels are now repeated for each of the former column labels in a new second column labeled 'Attribute', and a new third column labeled 'Value' is created to hold the former intersecting values. Below is an example of the before and after using the original mock sample from my first post.

Original Table 2

Category     Southwest          Northeast

----------     ------------         -------------

CatValue1    Bob Johnson      Betty Jackson

CatValue2    Jim Morris     

Unpivoted Table 2

Category     Attribute            Value

----------     ------------         -------------

CatValue1    Southwest         Bob Johnson

CatValue1    Northeast          Betty Jackson

CatValue2    Southwest         Jim Morris

Once I had the data in this format, I renamed the Attribute and Value columns to Region and Owner respectively, so if someone looks at this in the future it will hopefully make more sense to them. Finally, I created a new calculated column in the imported Table 1 that searched Table 2 for the value of Owner where both region and category matched, as shown below.

CategoryOwner = CALCULATE(VALUES('Table2'[Owner]),FILTER('Table2',AND ('Table2'[Region] = 'Table1'[Region],'Table2'[Category] = 'Table1'[Category])))

Now, when the feeder Excel file content is updated, simply hitting refresh, or waiting for the scheduled refresh should result in the reports updating properly...well, unless they paste in the entire table instead of just the data into the Excel feeder file, since Excel apparently treats this as a new table and gives it a new name.

If someone sees this and has a better way to do it, considering the limitations above, I'd love to hear it...perhaps especially so if it involves Power Query, as I really do need to learn it if I'm going to keep using Power BI lol.