Add column based on a list from another table

[clubspec]

Hi Experts,

I want to add a new column with look up value from another table with conditions.

i.e. New column in table 1 from Wkly Capacity column in table 2 but the conditons are:

WeekNum on table 1 = WeekNum on table 2 and WC (group) on table 1 = WC (Group) on table 2

Table 1Table 2

Both tables are currently linked separately to a calendar table on WeekNum, is this correct or I should create a relationship directly between Table 1 and Table 2?  If yes, link which column?

Thank you your help in advance,

Ray

[Ashish_Mathur]

Hi,

Write this calculated column in Table1

=CALCULATE(SUM('Table2'[Wkly capacity]),FILTER('Table2','Table2'[WeekNum]=EARLIER('Table1'[WeekNum])&&'Table1'[WC (Group)]=EARLIER('Table1'[WC (groups)])))

Hope this helps.

[clubspec]

Thank you Ashish,

I haven't tried yours but I took the simpler one which provided by Tom above.

Anyway, thank you also for the input.  I will try it myself later on another scenario.

Have a good day ahead.

Ray

[Ashish_Mathur]

You are welcome.

[TomMartens · Accepted solution]

Hey,

just create a calculated column in table 1 like so:

name of the column = 
LOOKUPVALUE(
    '<table 2>'[Wkly Capacity]
    ,'<table 2>'[WeekNum] , '<table 1>'[WeekNum]
    ,'<table 2>'[WC (Group)] , '<table 1>'[WC (groups)]
)

Hopefully this is what you are looking for.

Regards,

Tom

[clubspec]

Thank you Tom,

It works!

Have a good day ahead.