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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors