Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dmoetnogleren
Regular Visitor

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!

1 ACCEPTED SOLUTION
Dmoetnogleren
Regular Visitor

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

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

 

View solution in original post

3 REPLIES 3
Dmoetnogleren
Regular Visitor

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

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

 

v-yilong-msft
Community Support
Community Support

Hi @Dmoetnogleren ,

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

vyilongmsft_0-1718078736049.png

vyilongmsft_1-1718078784957.png

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

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

vyilongmsft_2-1718078991637.png

 

 

 

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.

Kaviraj11
Solution Sage
Solution Sage

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
 
Kaviraj11_0-1718034740158.png

 

Regards,

Kaviraj

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors