cancel
Showing results forย
Search instead forย
Did you mean:ย
New Member

## contar una columna en base a otra columna en otra tabla

Hola, espero me puedan ayudar.

tengo dos tablas, la TABLA_A tiene los campos de (id_region, Regiรณn, Ciudad), la segunda TABLA_B tiene los campos(id_clave,id_region,actividad,fecha), el caso es que quiero contar cuantas ciudades รบnicas fueron visitadas en cada actividad), tengo la siguiente formula

Total Ciudad = calculate(DISTINCTCOUNT(TABLA_A[Ciudad]),USERELATIONSHIP(TABLA_B[id_region],TABLA_A[id_region]))
Pero me cuenta los valores unicos de la TABLA_A, no me cuenta en base a la TABLA_B, osea me trae todas las ciudades existentes de cada region.
X ejemplo en la TABLA_B tengo varias actividades en la misma y distintas ciudades, no en todas, pero me cuenta como si hubiera estado en todas las ciudades.
Si me pueden ayudar gracias.

1 ACCEPTED SOLUTION
Super User III

If you have an active 1-many relationship betwee Table_A[idregion] and Table_B[idregion] this should work too, although I'd need some sample data to do a quick check

``````Total Ciudad =
CALCULATE ( DISTINCTCOUNT ( Tabla_A[Ciudad] ), Tabla_B )``````

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

3 REPLIES 3
Super User III

Hi @Glenan

It would help if you show a sample of the tables in your model and their relationships (active and inactive). Try this:

1. Place Tabla_B[Actividad] in a table visual

2.  Update the measure and place it in the visual:

``````Total Ciudad =
CALCULATE (
DISTINCTCOUNT ( Tabla_A[Ciudad] ),
FILTER ( Tabla_A, Tabla_A[id_region] = SELECTEDVALUE ( Tabla_B[id_region] ) )
)``````

If this does not work please show a sample of your tables

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Super User III

If you have an active 1-many relationship betwee Table_A[idregion] and Table_B[idregion] this should work too, although I'd need some sample data to do a quick check

``````Total Ciudad =
CALCULATE ( DISTINCTCOUNT ( Tabla_A[Ciudad] ), Tabla_B )``````

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

New Member

Thanks!!, the best solution it's the most simple

## Helpful resources

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors