Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
103 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |