Forum Discussion

nicholas_goodye's avatar
nicholas_goodye
Frequent Visitor
11 months ago
Solved

Ordering Detail Level Table by Total Count of Rows per Location

The data: LocationTable LocationID   LocationName 001 A 002 B 003 C IncidentTable LocationID   IncidentID    Date 003 1 10/1/25 003 2 7/1/25 003 ...
  • Zanqueta's avatar
    Zanqueta
    11 months ago

    Hello nicholas_goodye ,

    I tested the function bellow and worked here;



    CountOfIncidents_LastMonth = 
VAR StartOfLastMonth = EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), -1) 
VAR StartOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
RETURN
    CALCULATE(
        COUNTROWS('IncidentTable'),
        'IncidentTable'[Date] >= StartOfLastMonth,
        'IncidentTable'[Date] < StartOfThisMonth
    )


    ✅ If this answer solves your problem, please mark it as correct to help other community members.
     

Anonymous's avatar
Anonymous
Not applicable
11 months ago

Hi nicholas_goodye ,

Try below steps.

 

1. Create a Last Month Filter measure.

 

IsLastMonth =

VAR TodayDate = TODAY()

VAR StartOfThisMonth = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR StartOfLastMonth = EDATE(StartOfThisMonth, -1)

VAR EndOfLastMonth = EOMONTH(StartOfLastMonth, 0)

RETURN

'IncidentTable'[Date] >= StartOfLastMonth &&

'IncidentTable'[Date] <= EndOfLastMonth

 

Note: Use this as a visual filter = TRUE.

 

2. Create a measure that counts incidents per location (last month).

 

LastMonth Incident Count =

CALCULATE(

    COUNTROWS('IncidentTable'),

    KEEPFILTERS([IsLastMonth])

)

 

3. Create a measure to be used for sorting the rows. This measure returns the count per location and repeats it on every row, allowing sorting.

 

Sort By Incident Count =

CALCULATE(

    [LastMonth Incident Count],

    ALLEXCEPT('IncidentTable', 'IncidentTable'[LocationID])

)

 

Note: This is the key for ordering your detail table.

 

4.  In your table visual containing

LocationName, IncidentID, Date.

 

Sort by --> Sort By Incident Count --> Descending

 

 Sort by --> IncidentTable[Date] --> Ascending .

 

If my response as resolved your issue please mark it as solution and give kudos.

 

Thanks,

Dinesh