topic Re: Ordering Detail Level Table by Total Count of Rows per Location in DAX Commands and Tips

Ordering Detail Level Table by Total Count of Rows per Location

nicholas_goodye — Fri, 14 Nov 2025 18:29:35 GMT

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	3	10/15/25
001	4	10/1/25
002	5	10/1/25
002	6	10/10/25
002	7	7/1/25
002	8	10/15/25

Assuming the month is currently November, I am looking to be able to pull a list of Incidents from the previous month, ordered by detail the Location that has the most Incidents first. I want to see Location B show up first in my list, as it has 3 Incidents that meet my critera, then C with two instances, then A with 1. The resulting table in PowerBI should look like this:

LocationTable[LocationName]	IncidentTable[IncidentID]	IncidentTable[Date]
B	5	10/1/25
B	6	10/10/25
B	8	10/15/25
C	1	10/1/25
C	3	10/15/25
A	4	10/1/25

Currently, I am pulling this data only ordered by IncidentID. I am also using an "IsLastMonth" measure that is being used because for sending out a version of this report via Paginated Report. This can be removed if the solution to my problem here can bring in only last month's data via some filter, and not a hard coded method that happens in the background while using Power BI.

IsLastMonth =

VAR TodayDate = TODAY()

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

VAR StartOfLastMonth = EDATE(StartOfThisMonth, -1)

VAR EndOfLastMonth = EOMONTH(StartOfLastMonth, 0)

RETURN

IF (

CALCULATE(

COUNTROWS('IncidentTable'),

'IncidentTable'[Date] >= StartOfLastMonth,

'IncidentTable'[Date] < StartOfThisMonth // less than first day of this month

) > 0,

)

Hope this is clear enough of a question, thank you!

Re: Ordering Detail Level Table by Total Count of Rows per Location

Zanqueta — Fri, 14 Nov 2025 21:06:11 GMT

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.

Re: Ordering Detail Level Table by Total Count of Rows per Location

Praful_Potphode — Sat, 15 Nov 2025 04:11:58 GMT

Hi nicholas_goodye

i created one measure to show 1 or 0 for previous month as shown below.

previous month = IF( year([Today's Date])=YEAR(MAX(DimDate[Date])) && MONTH([Today's Date])-1=MONTH(MAX(IncidentTable[Date])),1 ,0 )

Today's Date = TODAY()

Today's Date is also a measure which you can customise.if you want month from slicer use SELECTEDVALUE function.

Sample PBIX can be downloade from here.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: Ordering Detail Level Table by Total Count of Rows per Location

Anonymous — Sat, 15 Nov 2025 20:04:43 GMT

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

Re: Ordering Detail Level Table by Total Count of Rows per Location

v-hashadapu — Wed, 19 Nov 2025 09:43:07 GMT

Hi nicholas_goodye , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by Anonymous is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

Thank you Anonymous for your valuable response.

Re: Ordering Detail Level Table by Total Count of Rows per Location

v-hashadapu — Mon, 24 Nov 2025 05:30:09 GMT

Hi nicholas_goodye , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.