Forum Discussion
Summarize and MAX not functioning as expected
Hi,
I have a table that looks like this
IDDateGenderSub-District
| 1 | 1/8/2019 | M | A |
| 2 | 2/8/2019 | M | A |
| 3 | 3/8/2019 | M | B |
| 4 | 3/8/2019 | F | B |
| 5 | 3/8/2019 | F | B |
| 6 | 10/8/2019 | F | C |
| 7 | 10/8/2019 | M | C |
| 8 | 21/8/2019 | F | D |
| 9 | 21/8/2019 | M | D |
| 10 | 25/8/2019 | M | D |
I've ran the following table DAX query and got the following result:
Table 6 = SUMMARIZE('table','table'[Sub-District],"Start_Date",MIN('table'[Date]),"End_Date",MAX('Table 2'[Date]),"Male",CALCULATE(count('table'[Gender]),'table'[Gender]="M"),"Female",CALCULATE(count('table'[Gender]),'table'[Gender]="F"))
Sub-DistrictEnd_DateMaleFemaleStart_Date
| Sub-District | Start_Date | End_Date | Male | Female |
| A | 1/8/2019 0:00 | 25/8/2019 0:00 | 2 | |
| B | 3/8/2019 0:00 | 25/8/2019 0:00 | 1 | 2 |
| C | 10/8/2019 0:00 | 25/8/2019 0:00 | 1 | 1 |
| D | 21/8/2019 0:00 | 25/8/2019 0:00 | 2 | 1 |
As you can note the MAX date column is not returning the right result as it basically maxed the date across the entire column.
Any help would be appreciated.
Best,
Dat_Data
Hi Anonymous
For End_Date you are using Table 2 instead of table:
"End_Date", MAX ( 'Table 2'[Date] )
When you SUMMARIZE over ' table', you have no filters applied on Table 2, unless there's a relationship
