Filter in data base

[yfquirogah]

I have this issue:

I have 2 tables, the first one shows Positions that have been removed, the date this happened and a column that indicates a code that belongs to an specific area; the second table shows the area this code belongs to. The codes table may change monthly, that is why I need to create a colum that according to the date the position was released it looks in that interval of data to find the area the code belonged to at the time.

First table:

OM Position ID	OM Position Name	Fecha de Baja	Mes	Month	Year	Ce.coste
10705627	GLOBAL BRANDS MANAGER	15/01/2020	Enero	1	2020	COPEC02203
10704392	LOCAL PREMIUM BRANDS MANAGER	11/01/2020	Enero	1	2020	COBA002070
10765089	PRODUCTION MANAGER	13/01/2020	Enero	1	2020	COPEC01613
10853367	TRANSFORMATION DIRECTOR	11/02/2020	Febrero	2	2020	COPEC02203
10703825	BREWING 1	28/02/2020	Febrero	2	2020	COBA002070
10704734	ZONE WATER & ENERGY	15/02/2020	Febrero	2	2020	COPEC01613
10705219	LEGAL DIVISION	30/03/2020	Marzo	3	2020	COPEC02203
10704005	BREWING TEAM 3	27/03/2020	Marzo	3	2020	COBA002070
10846442	PEOPLE EC DIRECTOR	22/03/2020	Marzo	3	2020	COPEC01613

Second table:

Month	Year	Ce.coste	Centro de coste	Function
1	2020	COPEC02203	BRAND MANAGEMENT	Marketing
1	2020	COBA002070	PREMIUM BRANDS MANAGEMENT	Marketing
1	2020	COPEC01613	ENGINEER MANAGEMENT	Supply
2	2020	COPEC02203	BRAND MANAGEMENT	Marketing
2	2020	COBA002070	PREMIUM BRANDS MANAGEMENT	Sales
2	2020	COPEC01613	ENGINEER MANAGEMENT	Supply
3	2020	COPEC02203	BRAND MANAGEMENT	Legal & CA
3	2020	COBA002070	PREMIUM BRANDS MANAGEMENT	Sales
3	2020	COPEC01613	ENGINEER MANAGEMENT	People

I tried to make the relationships but i keep receiving the same result that is basically a combination of the Position with each one of the code results for every month.

I have been looking around on how to resolve this, I found a post with a formula that goes this way:

Function =
CALCULATE (
    FIRSTNONBLANK ( Table2[Function], 1 ),
    FILTER (
        Table2,
        Table2[month] = Table1[month]
            && Table2[year] = Table1[year]
    )
)

I understand what the function is supposed to do, and I think in theory it should work but when I get to the filters I'm not able to access the information on table 1. 

I would really appreciate your help.

[V-lianl-msft · Accepted solution]

Hi @yfquirogah ,

Based on your description, you want to display "function" of Table 2 according to "month", "year" and "CE. Coste" in Table 1.

You can try to concatenate "month" "year" "CE. Coste" and create a relationship for the two tables.

Column = CONCATENATE('Table'[Year],CONCATENATE('Table'[Month],'Table'[Ce.coste]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Does this calculated column formula work?

Function = CALCULATE(FIRSTNONBLANK(Table2[Function],1),FILTER(Table2,Table2[month]=EARLIER(Table1[month])&&Table2[year]=EARLIER(Table1[year])&&Table2[Ce.Coste]=EARLIER(Table1[Ce.Coste])))

Hope this helps.