Summarize Tables with TopN / RankX Help

[ExcelMonke]

Hello, I am trying to get a summarized table that filters the TopN / Rank for a given aggregation. For some detail, I have the following table of data:
Table 1: "Encounters"

Dept ID	EncounterID	Date
111	32453245	1/1/2023
222	32462623	2/4/2023
333	65654333	3/7/2023
111	26587765	11/2/2022
111		11/4/2022
222	36874769	12/1/2023
333		8/3/2023
111	73658498	1/21/2023
444	47694362	7/9/2023

This table "Inter" has a relationship with the following table, one to many relationship to 'Encounter' based on DeptID

DeptID	DeptID2	DeptName
111	ABC	Jim's Facility
222	DEF	Bob's Facility
333	GHI	Jane's Facility
444	JKL	Mary's Facility

This table in turn is has a relationship one to many with table 3 "DeptListing" as following

DeptID2	DeptName
ABC	Jim's Facility
DEF	Bob's Facility
GHI	Jane's Facility
JKL	Mary's Facility

My intended result is this:

DeptName	Encounter Count
Jim's Facility	3
Bob's Facility	2
Jane's Facility	1
Mary's Facility	1

I have currently tried this to no avail:

Summarized Table by Top N = 
VAR _N = 4
VAR _SUMMARIZED =
    SUMMARIZE (Inter, 
    DeptListing[DeptName],
    "Encounter Count", COUNT(Encounters[EncounterID]))
VAR _ADDRANK =
    ADDCOLUMNS ( _SUMMARIZED, "RANK", RANKX ( _SUMMARIZED, [DeptName],, DESC ) )
VAR _TopN =
    SUMMARIZE ( FILTER ( _ADDRANK, [RANK] <= _N ),  [DeptName])
RETURN
    _TopN

Any help is appreciated!

[Anonymous · Accepted solution]

Hi @ExcelMonke ,

Below is my table1:

Below is my table2 :

Below is my table3:

The following DAX might work for you:

Measure = CALCULATE(
    COUNTROWS(Encounters),
    FILTER(Encounters,Encounters[Encounter ID]<>BLANK()),
    ALL(Encounters)
)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.