Forum Discussion
Solved
Groupby and lookup values between tables using DAX
Hi Community. Could you please help me wth the following scenario?
I have tables: Table_1 and Table_2. I am trying to populate column Desired_Value in Table_1.
This Desired_Value column should basically check for any Date (from Table_2) falling in the range of Start_Date and End_Date (from Table_1) for that particular ID and give me a grouped sum of Ind_Value (from Table_2). How to achieve this using DAX? Any help is appreciated.
Table_1:
| ID | Start_Date | End_Date | Desired_Value |
| 1 | 1/08/2021 | 15/07/2023 | 3 |
| 1 | 16/07/2023 | 1/08/2023 | 16 |
| 2 | 1/08/2021 | 22/09/2023 | 8 |
| 3 | 1/08/2021 | 23/09/2023 | 10 |
| 4 | 10/03/2022 | 17/03/2022 | 2 |
| 4 | 18/03/2022 | 1/04/2023 | 3 |
| 5 | 1/02/2021 | 18/08/2023 | 1 |
| 6 | 1/02/2020 | 16/06/2023 | 1 |
| 7 | 1/07/2021 | 9/09/2022 | 12 |
Table_2:
| ID | Date | Ind_Value |
| 1 | 12/07/2023 | 2 |
| 1 | 13/07/2023 | 1 |
| 1 | 21/07/2023 | 9 |
| 1 | 1/08/2023 | 7 |
| 2 | 12/07/2023 | 4 |
| 2 | 23/07/2023 | 4 |
| 3 | 4/08/2023 | 10 |
| 4 | 15/03/2022 | 2 |
| 4 | 29/03/2023 | 3 |
| 5 | 17/03/2023 | 1 |
| 6 | 15/04/2022 | 1 |
| 7 | 7/09/2021 | 5 |
| 7 | 8/09/2021 | 7 |
Desired Value = var i = [ID] var b = Filter(Table_2,Table_2[ID]=i && Table_2[Date] in GENERATESERIES([Start_Date],[End_Date])) return sumx(b,[Ind_Value])
