Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Measure for date range and TopN

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 TypeLocationDateFinancial Quarter
Missing personGlasgow02/08/2024Q2
Missing personGlasgow02/08/2024Q2
Medical concernGlasgow01/11/2024Q3
Missing personGlasgow01/11/2024Q3
Missing personGlasgow01/11/2024Q3
Medical concernEdinburgh02/08/2024Q2
Medical concernEdinburgh02/08/2024Q2
Missing PersonEdinburgh02/08/2024Q2
Medical concernEdinburgh01/11/2024Q3
Medical concernEdinburgh01/11/2024Q3
AggressionManchester02/08/2024Q2
AggressionManchester02/08/2024Q2
AggressionManchester01/11/2024Q3
AggressionManchester01/11/2024Q3
Medical concernManchester01/11/2024Q3

 

Thanks for your help.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

5 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      RichOB
      Icon for Post Partisan rankPost Partisan
      1 year ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • SacheeTh's avatar
    SacheeTh
    Icon for Resolver II rankResolver II
    1 year ago

    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.