Forum Discussion
countrows based on slicer & related table
Hi, I've got 2 tables - one with my list of clients - another with list of apointments related to clients with a date I want to set a date slicer in excel to get the number of apointments p...
v-danhe-msft
Microsoft Employee
Microsoft EmployeeHi E_hit,
Based on my test, you could refer to this formula:
nb_apointment = CALCULATE(
COUNT(apointment[code_apointment]),
FILTER (apointment,
apointment[Date_apointment]>= MAX('apointment'[date_apointment]) ))Result:
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hey,
Thanks for the answer. However it doesn't work as expected on my file (I am using power pivot for excel so I cannot check your power BI file)
The results I have in my column is the total number of appointment, whatever the user. Instead I would like the number of apointment per user
With data sample:
table apointment
| code_apointment | date_apointment | type_apointment | code_client |
| 323 | 23/05/2018 | A | 1234 |
| 565 | 24/05/2018 | B | 6523 |
| 546 | 20/02/2018 | C | 8965 |
| 135 | 08/09/2017 | C | 8965 |
the results I get on my client table
| code_client | nb_apointment | client_age |
| 1234 | 4 | 23 |
| 6523 | 4 | 36 |
| 8965 | 4 | 56 |
The results I want on my client table:
| code_client | nb_apointment | client_age |
| 1234 | 1 | 23 |
| 6523 | 1 | 36 |
| 8965 | 2 | 56 |
The tables are linked with the code_client