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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdatmain
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: 

 

Untitled.png

 

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 
DistrictRegion
1North
2North
3North
3East
4East
5East
6East
7South
8South
9South
10West
11West
12West

 

DATES 
DistrictDate
13/10/2021
23/9/2021
33/8/2021
45/30/2021
55/20/2021
65/22/2021
73/14/2021
83/19/2021
93/12/2021
103/10/2021
113/3/2021
123/4/2021

 

DESIRED OUTPUT 
RegionMin DateMax Date
North3/8/20213/10/2021
East3/8/20215/30/2021
South3/12/20213/19/2021
West3/3/20213/10/2021
1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
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))

V-lianl-msft_0-1616033930754.pngV-lianl-msft_1-1616033938828.png

 

 

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

 

View solution in original post

1 REPLY 1
V-lianl-msft
Community Support
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))

V-lianl-msft_0-1616033930754.pngV-lianl-msft_1-1616033938828.png

 

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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