cancel
Showing results 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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors