Forum Discussion

Dmoetnogleren's avatar
Dmoetnogleren
Regular Visitor
2 years ago
Solved

Filter question

Hello,

 

Can anyone help with the following?

 

I have a DIMtable like this:

IDName person
1X
2Y

 

And a Fact table like this:

IDStartdate
110-10-2020
21-8-2022
227-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!

  • Dmoetnogleren's avatar
    Dmoetnogleren
    2 years ago

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

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

     

3 Replies

  • Dmoetnogleren's avatar
    Dmoetnogleren
    Regular Visitor
    2 years ago

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

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

     

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.