countrows based on slicer & related table

[E_hit]

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!

[v-danhe-msft]

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.

https://www.dropbox.com/s/sx88kxa0sraht5g/countrows%20based%20on%20slicer%20%26%20related%20table.pb...

Regards,

Daniel He

[E_hit]

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