Forum Discussion
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:
| EID | First Name | Last Name | Type | Appointment Date | DOB |
| 123456 | A | 1/1/2020 | |||
| 123457 | A | 1/2/2020 | |||
| 123458 | B | 1/1/2020 | |||
| 123456 | A | 1/5/2020 | |||
| 123456 | A | 1/13/2020 |
This is what I want the report to show (filter by type):
Type A
| EID | First Name | Last Name | DOB | # of Appointments Booked | Appointment 1 | Appointment 2 | Appointment 3 |
| 123456 | 3 | 1/1/2020 | 1/5/2020 | 1/13/2020 | |||
| 123457 | 1 | 1/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
- amitchandak
Super User
Anonymous , Try like
new columns
Appoint No = "Appointment " + rankx(all(Table),[Appointment Date],,asc,Dense)
Use this a a column in matrixnew 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])amitchandak Thanks! I will try this out, but I am already coming to some issues. Perhaps I am doing something wrong?
- amitchandak
Anonymous , These are DAX columns and measures. You are creating in power Query
