Forum Discussion

PBI12345's avatar
PBI12345
Icon for Helper I rankHelper I
2 years ago
Solved

Dax request: Filter a table's values based on values from another table

Hello,

 

In the below scenario, I have two tables. I am trying to build a measure that returns the value from Table2 associated with the maximum date from Table1 where column Time = "Past".

 

In this example, I would like my measure to return a value of "700".


I have tried a few different calculations but am struggling to filter one table's date range based on another. Would connecting the tables with an inactive relationship and using RELATED work? 

 

Any help greatly appreciated!

 

 

Table1 
DateTime
Jan-24Past
Feb-24Past
Mar-24Past
Apr-24Past
May-24Past
Jul-24Past
Aug-24Past
Sep-24Future
Oct-24Future
Nov-24Future
Dec-24Future

 

Table 2 
DateValue
Jan-24100
Feb-24200
Mar-24300
Apr-24400
May-24500
Jul-24600
Aug-24700
Sep-24800
Oct-24900
Nov-241000
Dec-241100



  • Dangar332's avatar
    Dangar332
    2 years ago

    HI, PBI12345 

    If you have relationship b/w both tables on Date Column then try below measure.

    Measure =
var Max_date = calculate(max(table1[Date]),table1[time]="Past")
return
calculate(sum(table2[value]),Table2[date]=Max_date)



1 Reply

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    HI, PBI12345 

    If you have relationship b/w both tables on Date Column then try below measure.

    Measure =
var Max_date = calculate(max(table1[Date]),table1[time]="Past")
return
calculate(sum(table2[value]),Table2[date]=Max_date)