Measure for date range and TopN

[RichOB]

Hi, using the table below, I need a measure showing me the Top1 Location with the most "Medical Concerns" between 02/08/2024 = 01/11/2024 and the count, please = Edinburgh, 4.

Again I need this as a measure not just the visual filters, please.

Incident Type	Location	Date	Financial Quarter
Missing person	Glasgow	02/08/2024	Q2
Missing person	Glasgow	02/08/2024	Q2
Medical concern	Glasgow	01/11/2024	Q3
Missing person	Glasgow	01/11/2024	Q3
Missing person	Glasgow	01/11/2024	Q3
Medical concern	Edinburgh	02/08/2024	Q2
Medical concern	Edinburgh	02/08/2024	Q2
Missing Person	Edinburgh	02/08/2024	Q2
Medical concern	Edinburgh	01/11/2024	Q3
Medical concern	Edinburgh	01/11/2024	Q3
Aggression	Manchester	02/08/2024	Q2
Aggression	Manchester	02/08/2024	Q2
Aggression	Manchester	01/11/2024	Q3
Aggression	Manchester	01/11/2024	Q3
Medical concern	Manchester	01/11/2024	Q3

Thanks for your help.

[SacheeTh]

You can create a DAX measure to find the top location with the most "Medical Concerns" in the given date range by combining several functions, such as CALCULATE, FILTER, and TOPN.

Here's a DAX measure that should help you achieve your goal:

Top1 Location with Medical Concerns =
VAR StartDate = DATE(2024, 8, 2)
VAR EndDate = DATE(2024, 11, 1)

VAR FilteredTable =
    FILTER (
        YourTableName,
        [Incident Type] = "Medical concern" &&
        [Date] >= StartDate &&
        [Date] <= EndDate
    )
VAR GroupedCount =
    SUMMARIZE (
        FilteredTable,
        YourTableName[Location],
        "MedicalConcernsCount", COUNTROWS ( FilteredTable )
    )
VAR TopLocation =
    TOPN ( 1, GroupedCount, [MedicalConcernsCount], DESC )

RETURN
IF (
    COUNTROWS ( TopLocation ) > 0,
    CONCATENATEX (
        TopLocation,
        YourTableName[Location] & ", " & [MedicalConcernsCount],
        ", "
    ),
    "No data"
)

Explanation:

1. Define Start and End Dates:

   • StartDate and EndDate are set to 02/08/2024 and 01/11/2024, respectively.

2. FilteredTable:

   • This variable filters the data to include only rows where the Incident Type is "Medical concern" and where the Date falls between the specified start and end dates.

3. GroupedCount:

   • SUMMARIZE is used to group the filtered table by Location and count the number of rows (COUNTROWS) for each location.

4. TopLocation:

   • TOPN is used to get the location with the highest count of "Medical concern" incidents.

5. RETURN:

   • If there is a top location, CONCATENATEX returns the location and count as a string.
   • If there's no data in the given date range, it returns "No data".

This measure should give you the location with the most medical concerns within the given time frame, along with the count.

[Kedar_Pande]

@RichOB 

Top1LocationMedicalConcerns = 
VAR FilteredData = 
FILTER(
'Table', 
'Table'[Incident Type] = "Medical concern" && 
'Table'[Date] >= DATE(2024, 8, 2) && 
'Table'[Date] <= DATE(2024, 11, 1)
)
VAR Summary = 
SUMMARIZE(
FilteredData, 
'Table'[Location], 
"MedicalConcernsCount", COUNTROWS(FilteredData)
)
VAR TopLocation = 
TOPN(1, Summary, [MedicalConcernsCount], DESC)
RETURN
CONCATENATEX(TopLocation, 'Table'[Location], ", ") & ", " & 
MAXX(TopLocation, [MedicalConcernsCount])

In your case, it will return Edinburgh, 4.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bhanu_gautam]

@RichOB ,To create a measure in Power BI that shows the top location with the most "Medical Concerns" between 02/08/2024 and 01/11/2024, 

Top1LocationMedicalConcerns =
VAR FilteredTable =
FILTER(
'Table',
'Table'[Incident Type] = "Medical concern" &&
'Table'[Date] >= DATE(2024, 8, 2) &&
'Table'[Date] <= DATE(2024, 11, 1)
)
VAR GroupedTable =
SUMMARIZE(
FilteredTable,
'Table'[Location],
"MedicalConcernsCount", COUNT('Table'[Incident Type])
)
VAR TopLocation =
TOPN(
1,
GroupedTable,
[MedicalConcernsCount],
DESC
)
RETURN
CONCATENATEX(
TopLocation,
'Table'[Location] & ", " & [MedicalConcernsCount],
", "
)

[RichOB]

Hi @bhanu_gautam this is great thank you!

Sorry to be a pain but what would the measure look like if I didn't want the count number attached, Just the name of the top incident type, please?

[v-yiruan-msft · Accepted solution]

Hi @RichOB ,

bhanu_gautam  and Kedar have provided effective methods to obtain both the location and the top 1 count. You can make a slight adjustment to the measure below to include only the location:

Location with Top 1 Count = 
VAR _tab =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Incident Type] = "Medical concern" ),
        'Table'[Location],
        "@Count", COUNT ( 'Table'[Incident Type] )
    )
VAR _tab2 =
    TOPN ( 1, _tab, [@Count], DESC )
RETURN
    CONCATENATEX( _tab2, [Location],", " ,[Location],ASC)

Best Regards