Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a lot of appointmets for a lot of customers and I need to create some measures based on 'What number appointment is it, for that customer'.
I have data in a table like this
Date | Customer | Appointment | Status |
01-May | CMR-1 | App-1 | Complete |
02-May | CMR-2 | App-2 | Complete |
03-May | CMR-3 | App-3 | Cancelled |
04-May | CMR-1 | App-4 | Complete |
05-May | CMR-4 | App-5 | Complete |
06-May | CMR-1 | App-6 | Complete |
07-May | CMR-3 | App-7 | Complete |
08-May | CMR-2 | App-8 | Complete |
I need to know what number of appointment it is for that customer, where the appointment was completed (do not count cancelled). For example, the # Appointments column below.
Date | Customer | Appointment | Status | # Appointment | |
01-May | CMR-1 | App-1 | Complete | 1 | |
02-May | CMR-2 | App-2 | Complete | 1 | |
03-May | CMR-3 | App-3 | Cancelled | ||
04-May | CMR-1 | App-4 | Complete | 2 | |
05-May | CMR-4 | App-5 | Complete | 1 | |
06-May | CMR-1 | App-6 | Complete | 3 | |
07-May | CMR-3 | App-7 | Complete | 1 | |
08-May | CMR-2 | App-8 | Complete | 2 |
The measures I want are
For Example
First Time Appointments | 4 |
Average number appointments | 1.57 |
I am working with a tabular model and was hoping to use DAX to do all of this, but I cant work out how. Any help will be greatly appreciated 🙂
Hi @Anonymous ,
I too do not understand in the average bit. But for the First Time Appointment you can do the following.
Create 2 Calculated Column
What does this mean?
2. Average number of appointments - Take an average across the # Appointment data
You can't just add up the numbers in the column # Appointment and divide by the number of days. It makes no sense whatsoever since this column just holds the order number of the appointments for each individual customer. And if you think it does... then I'd be glad to hear your interpretation. In reality, what you're suggesting to do is similar to this (assume that we're dealing with 1 customer only for simplicity's sake):
1. Number the rows in a table from 1 to n.
2. Take the average (1+2+...+n) / n = n*(n+1) / (2*n) = (n+1) / 2.
What meaning does the above have? Let's see... Say n = 11 in a month (meaning there were 11 visits in a month from a single customer). The average you're suggesting is 6. So... what meaning do you attach to such a number?
I think you want to calculate something different. You want to know the average number of visits in the selected period of time but the averaging is over the customers, not days. This is a completely different calculation and it does have a well-defined meaning, namely, it answers the question: What is the average number of visits from each customer in the selected period of time?
Best
D
Hi @Anonymous
For your # Appointment calculated column you can do (assuming the Date column is of date type). From that you can get to the rest
NumAppointment =
VAR currentdate_ = Table[Date]
RETURN
CALCULATE (
COUNT ( Table1[Appointment] ),
ALLEXCEPT ( Table1, Table1[Customer] ),
Table1[Date] < currendate_,
Table1[Status] <> "Cancelled"
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |