cancel
Showing results for
Did you mean:
Regular Visitor

## How to get min date of group based on related table using DAX meaure?

I have a REGIONS table with District and attributed Region. I also have a DATES table, with each unique district having a date. I want to get the minimum date per region. Here is a photo example:

There can be multiple regions per district. District 3 belongs to both North and East, and district 3 also has the min date for each. So in the desired otuput, both North and East have 3/8/2021 as the minimum date.

Typically I would do a join on District and take the minimum date per each region group, but I am new to dax and am unsure how to create the measure. I have used Calculate with Min, but I can't figure out. Much appreciated for any help.

 REGIONS District Region 1 North 2 North 3 North 3 East 4 East 5 East 6 East 7 South 8 South 9 South 10 West 11 West 12 West

 DATES District Date 1 3/10/2021 2 3/9/2021 3 3/8/2021 4 5/30/2021 5 5/20/2021 6 5/22/2021 7 3/14/2021 8 3/19/2021 9 3/12/2021 10 3/10/2021 11 3/3/2021 12 3/4/2021

 DESIRED OUTPUT Region Min Date Max Date North 3/8/2021 3/10/2021 East 3/8/2021 5/30/2021 South 3/12/2021 3/19/2021 West 3/3/2021 3/10/2021
1 ACCEPTED SOLUTION
Community Support

Hi @mdatmain ,

Try to create measures like below:

``Max date = CALCULATE(MAX(DATES[Date]),ALLEXCEPT(DATES,DATES[District]),CROSSFILTER(DATES[District],REGIONS[District],Both))``
``Min date = CALCULATE(MIN(DATES[Date]),ALLEXCEPT(DATES,DATES[District]),CROSSFILTER(DATES[District],REGIONS[District],Both))``

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

Community Support

Hi @mdatmain ,

Try to create measures like below:

``Max date = CALCULATE(MAX(DATES[Date]),ALLEXCEPT(DATES,DATES[District]),CROSSFILTER(DATES[District],REGIONS[District],Both))``
``Min date = CALCULATE(MIN(DATES[Date]),ALLEXCEPT(DATES,DATES[District]),CROSSFILTER(DATES[District],REGIONS[District],Both))``

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors