TopN of TopN

[tviv23]

I have a table, Outpatient Referrals with fkMedClassID and fkAdmittingDiagnosisID columns. It has a many to one relationship to the pks on the dim tables MedClass and Diagnosis_Admitting tables. There are also many to one relationships to the department and date dim tables used to filter.

I have a business need to give, using the Outpatient Referrals table as the base, the top 5 MedClass.PrimaryMedClasDscr and for each of the 5 I need the top 3 Diagnosis_Admitting.MedClassDxSubCategory. Both need to be filterable by any report or visual filters.

I've only been doing this a month or so and have managed to get by so far with examples online. I can't seem to find a relevant example for this with counts. All of the topn and rankx examples I've found seem to be with sums of columns from the main table like a total amount.  I thought i'd be able to use the 2 dim table fields and do a top n in the filter but it will only let me do one per filter. I'm not even sure what visuals would best display this at this point.  Any help at all would be greatly appreciated.

OP Table

fkMedClassID	fkDiagnosisID
1	1
1	1
1	2
1	2
1	2
1	5
45	9
45	45
45	9
45	6
45	6
45	7
45	45
96	6
96	6
96	7
96	7
96	7
96	1
87	88
87	88
87	7
87	4
87	4
99	1
99	2
99	3
52	55
48	68

MedClass table

pkMedClassID	Desc
1	class 1
45	class 45
96	class 96
87	class 87
99	class 99
52	class 52
48	class 48

Diagnosis Table (Dx)

pkDiagnosisID	Desc
1	Dx1
2	Dx2
3	Dx3
4	Dx4
5	Dx5
6	Dx6
7	Dx7
9	Dx9
45	Dx45
55	Dx55
68	Dx68
88	Dx88

Results (counts)

Top 5 MedClass by count	Top 3 Dx by count
Class 45 (7)	Dx9 (2)
	Dx45 (2)
	Dx6 (2)
class 1 (6)	Dx2 (3)
	Dx1 (2)
	Dx5 (1)
class 96 (6)	Dx7 (3)
	Dx6 (2)
	Dx1 (1)
class 87 (5)	Dx88 (2)
	Dx4 (2)
	Dx 7 (1)
class 99 (3)	Dx 1 (1)
	Dx 2 (1)
	Dx 3 (1)

the results would be displayed in maybe a stacked bar graph based on the actual counts from the OP table of the Top 5 and the actual counts fromt the OP table of the Top 3 Dx within those Top 5. So, not a count of all the Top 3 records, just a count of the Top 3 only within each MedClass in the Top 5.

[Anonymous]

Hi @tviv23 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

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

[Anonymous · Accepted solution]

Hi @tviv23 ,

Please refer to my pbix file to see if it helps you.

Create relationships between the tables.

Then create columns.

times =
CALCULATE (
    COUNT ( 'OP Table'[fkMedClassID] ),
    FILTER (
        ALLSELECTED ( 'OP Table' ),
        'OP Table'[fkMedClassID] = EARLIER ( 'OP Table'[fkMedClassID] )
    )
)

sort =
RANKX (
    'OP Table',
    'OP Table'[times] * 1000 + 'OP Table'[fkMedClassID],
    ,
    DESC,
    DENSE
)

column_sort =
VAR _times = 'OP Table'[times]
VAR text_ = "(" & _times & ")"
RETURN
    text_

 Then create two measures.

medclassdesc =
VAR _times =
    MAX ( 'OP Table'[times] )
VAR text_ = "(" & _times & ")"
VAR classmeddesc =
    MAX ( 'Medclass Table'[Desc] )
RETURN
    classmeddesc & MAX ( 'OP Table'[column_sort] )

diagnosis = MAX('Diagnosis Table (Dx)'[Desc])

If I have misunderstood your meaning, please provide more details with youe desired output.

Best Regards

Community Support Team _ Polly

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

[tviv23]

Thanks for taking the time to propose a solution. Trying to get some time to evaluate.

[Whitewater100]

Hi:

I think some sample data would help with an example of expected results. Thanks..

[tviv23]

Thanks added. Hopefully that helps.