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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Creating Reports: Unique IDs transposing data, and frequencies

Hello, I recently started working with Power BI and have quite a few questions. I am working off an excel file that I cleaned up prior and loaded it into Power BI. This is a report that documents every instance a client has booked an appointment, with all the intake information. However, I want to pull a report to show how many appointments an individual has booked in a clean way.

 

Below is an example of what the raw data looks like:

EIDFirst NameLast NameTypeAppointment DateDOB
123456  A1/1/2020 
123457  A1/2/2020 
123458  B1/1/2020 
123456  A1/5/2020 
123456  A1/13/2020 

 

This is what I want the report to show (filter by type):

 

Type A

EIDFirst NameLast NameDOB# of Appointments BookedAppointment 1Appointment 2Appointment 3
123456   31/1/20201/5/20201/13/2020
123457   11/2/2020  

 

As I am playing around, the names are aggregated together and my dates are adding up together and it is looking very funky. I would do this individually for each unique ID on my own, but there are over 240 clients that booked more than one appointment and that will take forever. Any assistance is appreciated! 

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , Try like

new columns
Appoint No = "Appointment " + rankx(all(Table),[Appointment Date],,asc,Dense)
Use this a a column in matrix

 

new column
# of Appointments = countx(filter(Table,[EID] =earlier([EID])),[Appointment Date])

or new measure measure
# of Appointments = countx(filter(allselected(Table),[EID] =max([EID])),[Appointment Date])

Anonymous
Not applicable

@amitchandak Thanks! I will try this out, but I am already coming to some issues. Perhaps I am doing something wrong?

 

saminoor_0-1608175738847.png

 

@Anonymous , These are DAX columns and measures. You are creating in power Query

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors