Forum Discussion

ExcelMonke's avatar
ExcelMonke
Impactful Individual
2 years ago
Solved

Summarize Tables with TopN / RankX Help

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
22232462623

2/4/2023

333656543333/7/2023
1112658776511/2/2022
111 11/4/2022
2223687476912/1/2023
333 8/3/2023
111736584981/21/2023
444476943627/9/2023

 

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

DeptID  DeptID2  DeptName  
111ABCJim's Facility
222DEFBob's Facility
333GHIJane's Facility
444JKLMary's Facility

 

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

DeptID2  DeptName  
ABCJim's Facility
DEFBob's Facility
GHIJane's Facility
JKLMary's Facility

 

My intended result is this:

DeptNameEncounter Count
Jim's Facility3
Bob's Facility2
Jane's Facility1
Mary's Facility1

 

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.