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 per clients at a given date / date range
I have created a measure :
Date_selected:=max(tbmkg_apointment[Date_apointment])
and a calculated column in my client table but I either go the total number of apointment non related to distinct client with this formula
CALCULATE(
COUNTROWS(tbmkg_apointment);
FILTER (tbmkg_apointment;
tbmkg_apointment[Date_apointment]>= tbmkg_apointment[Date_selected] ) )
or a results taht take into account all apointments for one client even if only one of them is at the desired date:
CALCULATE(
COUNTROWS(RELATEDTABLE(tbmkg_apointment));
FILTER (RELATEDTABLE(tbmkg_apointment);
tbmkg_apointment[Date_apointment]>= tbmkg_apointment[Date_selected] ) )
Any insights?
Below is a data sample:
table client
| code_client | nb_apointment | client_age |
| 4641231254 | 1 | 23 |
| 4641231255 | 1 | 36 |
table apointment
| code_apointment | date_apointment | type_apointment | code_client |
| 323 | 23/05/2018 | A | 4641231254 |
| 565 | 24/05/2018 | B | 4641231255 |
I want my calculate field in my client table in nb_apointment
Thanks!
2 Replies
v-danhe-msftMicrosoft Employee
Hi 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
