count the total of the agents in each category according to the first two characters

[Jeffreyjar]

I want to create a measure to count the total of agents in each category based on the first two characters and base of their status(column ESTATUS) 

We have two tables

One is "agent list "

EAAGTNAME	EAADDR1	EACITY	EAFCR	EASTATUS	New Agents Codes
DIKOBE KONG CARINE MARIANNE	YAOUNDE		A	A19906001
NGAGNI LAURENT	YAOUNDE		A	A19999010
BILOA JOSEPHINE	YAOUNDE		A	A19999035
TCHEUKO ROGER	YAOUNDE		A	A29999002
POUNGOM EPSE NGAMALEU DENISE	YAOUNDE		A	A29999005

The other one is table "AM"

N°	CODE AGENCE	AGENCE	NOM DU CHEF D'AGENCE
1	A1	YAOUNDE I	MBA ELIE
2	A2	YAOUNDE II	NANTCHOUANG ESTHER
3	A3	YAOUNDE III	MEFFO ZOUNDA NICOLE
4	A4	YAOUNDE IV	TCHOUKEU EPSE SIMO REGINE
5	A5	YAOUNDE V	TEDECK JEAN LEOPOLD
6	A6	YAOUNDE VI	ZOUMA PARFAIT DESIRE
7	A7	YAOUNDE VII	KENGUELIBOU EPSE E. AMBROISINE
8	A8	BERTOUA	BOUMSONG BOUMSONG SOPHONIE S
9	A9	YAOUNDE VIII	YEBECK JOSEPHINE
10	B1	DOUALA I	NDEFO ETIENNE
11	B2	DOUALA II	NONO MOUKETEY JOSIANE MELANIE
12	B3	DOUALA III	NSOM MICHAEL ANGOH
13	B4	DOUALA IV	NDIKUM EDITH SWIRI EPSE FONKAH
14	B5	DOUALA V	ANGOA OHANA JEAN BAPTISTE
15	B6	LIMBE	ENOW PIUS OROCK
16	B7	BAFOUSSAM	TAGNE BERNARD
17	B8	BAMENDA	TARDZENYUY STEPHEN
18	B9	GAROUA	HAMADOU BOUBA
19	C2	NGAOUNDERE	ABE NGA SYMPHORIEN VITAL
20	C3	KRIBI	NGATOUM GUY LEONARD
21	C4	DOUALA 7	OLI BELONG
22	C5	DOUALA 6	PENE TINA EP.NGATCHOU JOELLE L
23	C6	EBOLOWA	OWONA EYENE JEAN DEDIEU
24	C1	MAROUA	HEUDAIBE BENJAMIN

For example the code A19906001(New Agents Codes column) of the agent list table  will belong to the agency(CODE AGENCE) A1 because the first two characters of the code is A1 and the status(ESTATUS) is A

[Jeffreyjar]

Hi @v-chenwuz-msft 

thanks for the reply i've tried it  but nothing appears as expected 

This is what im getting 

This my relationships

can i send you the .pbix file by mail? im unable to send it here

[v-chenwuz-msft]

Hi @Jeffreyjar ,

First, create a new column named New Agents Codes via 

New Agents Codes = LEFT([EASTATUS],2)

to get the first two characters.

Then, create a relationship between these two tables based on New Agents Codes and CODE AGENCE.

Last, drag New Agents Codes and CODE AGENCE field to table visual and set the aggregation of [New Agents Codes] as count. Refer the following screenshot.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.