DAX Trouble with count and Max Date

[Anonymous]

Hi Everyone!

I'm having any difficult with a measure that I need to create.

My scenario is as follow:

TABLE1

usu_id_i	name_nv
60006	Petronila
60027	OSCAR
60029	Sandra
60030	María
60031	Carlos
60034	Rocio

TABLE2

id	usu_id_i	ge_categoria_id	fecha_inicio
1	60006	1	30/08/2016
98121	60027	1	30/09/2016
98158	60029	1	11/11/2016
98160	60030	1	17/11/2016
98161	60031	1	18/11/2016
98162	60034	1	18/11/2016
98165	60006	2	18/11/2016
98166	52	1	18/11/2016
98169	60030	2	18/11/2016
98170	60006	1	21/11/2016
126132	23	1	20/02/2017
126133	8078	2	20/02/2017
126134	60031	2	20/02/2017

I need a measure that count the actual number of clients by category. As you can see the clients can change between categories do I need to group the client in the most recent category. In this case:

ge_categoria_id	Clients
1	4
2	2

Anyone can help me?
Thanks

[v-shex-msft]

Hi @Anonymous,

You can try to use below sample if it suitable for your requirement.

Steps:

1. Filter the not contains records.

Filtered Table = FILTER(Sheet1,CONTAINS(Sheet2,[usu_id_i],[usu_id_i]))

2. Add a calculated column to analysis the count of date range based on 'category id' group:

Count = 
var compare_range = 1
return
COUNTX(FILTER('Filtered Table',[ge_categoria_id]=EARLIER([ge_categoria_id])&&[fecha_inicio]>=EARLIER([fecha_inicio])-compare_range&&[fecha_inicio]<=EARLIER([fecha_inicio])+compare_range),[fecha_inicio])

 

 3. Summary these records.

Result = SUMMARIZE('Filtered Table',[ge_categoria_id],"Clients",MAX('Filtered Table'[Count]))

 

Notice: you can modify the variable 'compare_range' to switch the compare range. Step 1 is an option, you can skip it if you need.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks @v-shex-msft for the answer but I have tried and It didn't work.
The result of the calculations is wrong.

And I would rather use a measure.

[v-shex-msft]

Hi @Anonymous,

>>The result of the calculations is wrong.

Can you share some detail information about these?

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Finally I found a solution.

I have created a column calculated in TABLE1 with the current category of the client.
It was easier than I expected.

Thanks for your collaboration.

[Vvelarde]

@Anonymous

Hi, what happen with users 52,23,8078 do you want to count them too

Because including it the result will be 6 & 3.

Victor

Lima - Peru

[Anonymous]

Hi @Vvelarde,

Users 52, 23 and 8078 are not included because they aren't in my TABLE1.