Need advice on creating a visual with slicer(s)

[Anonymous]

Hey All,

This is my data model:

• Region
  • City
    • Client

Each client is related to 1 city, but each city can be related to more than 1 region.

I am trying to create a visual to count the # of clients per region. Ya, it will create duplicates and paralel clients that exist in more than 1 region at the same time. And a slicer on the region.

How can I achieve this? I am not sure and would love some advise and directions.
Here is another graphical explanation of my data model.

Edit:
When trying to "LOOKUPVALUE" the region according to the city, I am getting an error

A table of multiple values was supplied where a single value was expected.

Which makes sense with this situation.

I have the city in the client table, but the region is mapped in other table(s).

Thanks
A

[v-piga-msft]

Hi @Anonymous ,

If it is convenient, could you show your sample data in text-tabular format in addition to (or instead of)  screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Best  Regards,

Cherry

[Anonymous]

Hey,
Here is a sample of my Region-City table

So,

The city named 'Ako' is mapped to 4 different regions (BEG,EUR,HEA,JAN).

The city named 'Alb' is mapped to 3 different regions (HEA,JAN,WOL).

The city named 'Bom' is mapped to 5 different regions (EUR,HEA,JAN,KIA,SHO).

Region          City Short

Beg	Ako
Eur	Ako
Hea	Ako
Jan	Ako

Hea	Alb
Jan	Alb
Wol	Alb

Eur	Bom
Hea	Bom
Jan	Bom
Kia	Bom
Sho	Bom

Here is a sample of my clients table

10 clients are mapped to 'Alb' and 0 to 'Ako' and 5 to 'Bom'

9446	Alb
2534	Alb
5144	Alb
6031	Alb
477	Alb
476	Alb
478	Alb
791	Alb
792	Alb
1466	Alb

94	Bom
149	Bom
223	Bom
604	Bom
742	Bom
990	Other

The expeced results are

Region - Count(# of Clients)

Beg- 0

Eur - 5 (0+5)

Hea - 15 = (0+5+10)

Jan - 15 = (0+5+10)

Kia - 5

Sho - 5

Cheers!