Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |