Forum Discussion

yfquirogah's avatar
yfquirogah
Helper I
6 years ago
Solved

Filter in data base

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 IDOM Position NameFecha de BajaMesMonthYearCe.coste
10705627GLOBAL BRANDS MANAGER15/01/2020Enero12020COPEC02203
10704392LOCAL PREMIUM BRANDS MANAGER11/01/2020Enero12020COBA002070
10765089PRODUCTION MANAGER13/01/2020Enero12020COPEC01613
10853367TRANSFORMATION DIRECTOR11/02/2020Febrero22020COPEC02203
10703825BREWING 128/02/2020Febrero22020COBA002070
10704734ZONE WATER & ENERGY15/02/2020Febrero22020COPEC01613
10705219LEGAL DIVISION30/03/2020Marzo32020COPEC02203
10704005BREWING TEAM 327/03/2020Marzo32020COBA002070
10846442PEOPLE EC DIRECTOR22/03/2020Marzo32020COPEC01613

 

 

Second table:

MonthYearCe.costeCentro de costeFunction
12020COPEC02203BRAND MANAGEMENTMarketing
12020COBA002070PREMIUM BRANDS MANAGEMENTMarketing
12020COPEC01613ENGINEER MANAGEMENTSupply
22020COPEC02203BRAND MANAGEMENTMarketing
22020COBA002070PREMIUM BRANDS MANAGEMENTSales
22020COPEC01613ENGINEER MANAGEMENTSupply
32020COPEC02203BRAND MANAGEMENTLegal & CA
32020COBA002070PREMIUM BRANDS MANAGEMENTSales
32020COPEC01613ENGINEER MANAGEMENTPeople

 

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.

Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.