Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
siddiq8686khi
Frequent Visitor

Help Required - TopN of Main and Subcategory

Hello Everyone,

 

I need help. I have a data of patients who visited hospital for their routine checkups. I have 4 data table for my analysis.

 

  • Patients visit and demographic table
  • Patient complaint table
  • Patient Diagnosis table
  • Patient medicine table

All the table have one main category column and one subcategory column. The sample data table of Complaint table is given below for your help:

Patient_ID

Date

Diagnosis_MainCategory

Diagnosis SubCategory

PAT-10019

24/05/2021

Anaemia

Type-1 Anaemia

PAT-10019

30/03/2022

Anaemia

Type-2 Anaemia

PAT-10019

25/05/2021

Anaemia

Type-2 Anaemia

PAT-10019

25/05/2022

Anaemia

Type-3 Anaemia

PAT-10019

28/03/2022

Anaemia

Type-3 Anaemia

PAT-10019

06/10/2021

Anaemia

Type-3 Anaemia

PAT-10019

09/11/2021

Anaemia

Type-3 Anaemia

PAT-10019

03/01/2022

Anaemia

Type-4 Anaemia

PAT-10019

21/01/2021

Anaemia

Type-4 Anaemia

PAT-10019

24/05/2021

Anaemia

Type-4 Anaemia

PAT-10019

16/02/2022

Anaemia

Type-4 Anaemia

PAT-10197

10/05/2022

Pneumonia

Type-1 Pneumonia

PAT-10197

29/01/2021

Pneumonia

Type-2 Pneumonia

PAT-10197

17/06/2021

Pneumonia

Type-2 Pneumonia

PAT-10197

18/03/2022

Pneumonia

Type-3 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-3 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-3 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

PAT-10197

30/12/2020

Pneumonia

Type-4 Pneumonia

 

All these table connected with patient ID in data model. I need to calculate the below mentioned measure from the above 4 tables.

  • TOP 3 and Other for Main Category and Subcategory for all the tables
  • I want to show a chart of “OTHERS” in a tooltip. Here the OTHER is all those other than top 3.
  • I showed all the Complaints, diagnosis, and Medications in donut chart. I want that when I click any complaint the diagnosis chart cross filters and showed top 3 diagnosis and medication chart show top 3 medicine of the selected item in complaint chart.

I have created my TOPN, and others measure but it not working dynamically, and I am unable to open OTHERS category in a separate chart.

I will share my sample PBIX file if required.

@tamerj1 

 

Thanks,

Sid

 

 

4 REPLIES 4
siddiq8686khi
Frequent Visitor

Dear All,

 

 Below is the link of my Sample PBIX file for your help

 

https://www.dropbox.com/s/p6lw8yznb3hjwob/Sample_Data.pbix?dl=0

 

I have calculated top 3 + Others but i am unable to show the complaints which are falling in REMAINING CATEGORY if we use Top 3.

 

Thanks,

Sid

 

Looking for help

Hello All,

 

Any suggestions from you ??

@amitchandak Thanks for the reply. Actually i also need to calculate "Others ( All those who left after TopN will be shown as Others in Chatrts) means, if we will show a donut chart then there will be 6 slices - 5 slices for top5 and 1 for Others.

 

Thanks,

Sid

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.