Forum Discussion
Lookup for specific value
Hi, I have two table below and would like to know how can I lookup the value base on certain condition.
Table1:
| Date |
| 15/02/2022 |
| 21/03/2022 |
| 12/04/2022 |
| 01/05/2022 |
| 11/06/2022 |
| 16/07/2022 |
| 01/08/2022 |
Table2:
| Date | Value |
| 01/02/2022 | 1 |
| 01/03/2022 | 2 |
| 01/04/2022 | 3 |
| 01/05/2022 | 4 |
| 01/06/2022 | 5 |
| 01/07/2022 | 6 |
| 01/08/2022 | 7 |
I am looking for a equation that can return the value from table 2 "Value"
For example when my timeline slicer selected 15/02/2022 - 12/04/2022, it will return the Max value in table 2 for the selected month, which is 3 in this case.
If I select 21/03/2022 - 16/07/2022, it will return 6 as the value.
Whatever period I selected, the equation will return the same month value from table 2.
Thank you.
Hi,
I am not sure if I understood your question and your datamodel correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: = VAR _maxdatetable2 = MAXX ( FILTER ( Table2, Table2[Date] <= MAX ( Table1[Date] ) ), Table2[Date] ) RETURN CALCULATE ( MAX ( Table2[Value] ), Table2[Date] = _maxdatetable2 )
1 Reply
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question and your datamodel correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: = VAR _maxdatetable2 = MAXX ( FILTER ( Table2, Table2[Date] <= MAX ( Table1[Date] ) ), Table2[Date] ) RETURN CALCULATE ( MAX ( Table2[Value] ), Table2[Date] = _maxdatetable2 )