Dax Filter one table based on two columns of another table

[tomperro]

I have two tables that have a relationship on Employee (EventTable filters TestsTable) but I also need to EventTable.Category to filter Category.TestsTable.

I need to show when the last Test in the same catgory was completed by each employee.

Example:  Tom has had 1 event in Category 1 (EventTable) and his last test in Category 1 was 12-31-2022 (TestsTable).

EventTable

Date	EventId	Employee	Category
1-1-2023	1	Tom	1
2-2-2023	2	Pete	2
3-3-2023	3	Steve	4

TestsTable

Date	Category	Employee
10-1-2022	1	Tom
12-31-2022	1	Tom
1-1-2022	3	Tom
2-2-2023	2	Pete
3-3-2023	3	Steve
4-4-2023	5	Steve

[Anonymous · Accepted solution]

Hi  @tomperro ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _maxdate=MAXX(FILTER(ALL(TestsTable),'TestsTable'[Category]=MAX('TestsTable'[Category])),[Date])
var _eventdate=MAXX(FILTER(ALL(EventTable),'EventTable'[Category]=MAX('TestsTable'[Category])),'EventTable'[Date])
return
IF(
    _maxdate=_eventdate,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Disconnect the tables and use TREATAS to project the filter from one table to the other.