topic Re: Filter question in DAX Commands and Tips

Filter question

Dmoetnogleren — Mon, 10 Jun 2024 11:21:58 GMT

Hello,

Can anyone help with the following?

I have a DIMtable like this:

ID	Name person
1	X
2	Y

And a Fact table like this:

ID	Startdate
1	10-10-2020
2	1-8-2022
2	27-1-2023

I want from everyone the most recent startdate in the DIM table. Person 1 had only one startdate (10-10-2020), so I want 10-10-2020 in the return as most recent start date. For person 2 I want 27-1-2023 returned as most recent startdate.

Thank you in advance!

Re: Filter question

Kaviraj11 — Mon, 10 Jun 2024 15:53:17 GMT

Hi,

Please create a measure as below and make sure to have a relationship between ID from two tables:

MostRecentStartDate =

VAR MaxDatePerID =

CALCULATE(

MAX('Fact'[StartDate]),

ALLEXCEPT(Dim, Dim[ID])

)

RETURN

MaxDatePerID

Regards,

Kaviraj

Re: Filter question

Anonymous — Tue, 11 Jun 2024 04:11:11 GMT

Hi Dmoetnogleren ,

I create two tables and create One-to-Many relationship.

Then I create a measure and here is the DAX code.

RecentDate = CALCULATE( MAX(FactTable[Startdate]), FILTER( FactTable, FactTable[ID] = RELATED(DIMtable[ID]) ) )

Best Regards

Yilong Zhou

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

Re: Filter question

Dmoetnogleren — Tue, 11 Jun 2024 06:12:11 GMT

Thank you for your answers. I cannot get it working, but I found another solution:

RecentStartDate = LASTDATE('Fact'[Start Date])