Forum Discussion
Solved
Add column based on double filter
I have two tables like this:
| Company | StartDate | EndDate | Regio |
| a | 01/01/2020 | 08/08/2020 | Demo |
| b | 01/01/2020 | 08/08/2020 | Demo |
| c | 01/01/2020 | 08/08/2020 | Demo |
| d | 01/01/2020 | 08/08/2020 | Demo |
| d | 08/01/2020 | 08/10/2020 | Live |
| d | 10/10/2020 | 01/12/2020 | Test |
| e | 01/01/2020 | 08/08/2020 | Demo |
| f | 01/01/2020 | 08/08/2020 | Demo |
| g | 01/01/2020 | 08/08/2020 | Demo |
| Company | LoginDate | regio |
| a | 02/01/2020 | demo |
| a | 03/01/2020 | demo |
| b | 04/01/2020 | demo |
| b | 05/01/2020 | demo |
| c | 06/01/2020 | demo |
| c | 07/01/2020 | demo |
| d | 11/11/2020 | test |
| d | 09/01/2020 | demo |
| d | 10/01/2020 | demo |
| e | 11/01/2020 | demo |
I need to add the regio in the second table by a double filter in the first table.
So I need to check the company and if the LoginDate is between start and end time. If so add regio to the second table.
How can i write the function in add column?
Hi DionTN
Can you try as below please?
Column = CALCULATE ( SELECTEDVALUE(Table1[Regio]), FILTER ( Table1, Table1[Company] = Table2[Company] && Table1[StartDate] <= Table2[LoginDate] && Table1[EndDate] >= Table2[LoginDate] ) )
1 Reply
Hi DionTN
Can you try as below please?
Column = CALCULATE ( SELECTEDVALUE(Table1[Regio]), FILTER ( Table1, Table1[Company] = Table2[Company] && Table1[StartDate] <= Table2[LoginDate] && Table1[EndDate] >= Table2[LoginDate] ) )