search for a value and return multiple fields

[4pepesanchez4]

Good afternoon:

First of all, thank the people who answer it. What i need to do is the following:

If they match a field (country) return me ALL the fields that meet the condition (city)

Thank you very much in advance,

Greetings.

[tamerj1 · Accepted solution]

Hi @4pepesanchez4 
You can use a table or matrix visual

Measure 

Cities = 
CONCATENATEX (
    Countries,
    Countries[city],
    " | "
)

[4pepesanchez4]

HI @tamerj1 !

How should I write the formula in Power Pivot knowing that the relationship between countries and tables is already done and that Country is in a different table than city?

• Country = Nombre y clienta (Related to each other)
• City = description field

The formula?

Thanx you a lot!!

[tamerj1]

Hi @4pepesanchez4 

just change the table name to the cities table

Cities = 
CONCATENATEX (
    Cities,
    Cities[city],
    " | "
)

[4pepesanchez4]

hi again 😅 @tamerj1 

Could you help me understand why the formula gives me an error? The first field that the formula asks me for is the table (Ventas_Clientas_Origen), the expression (Nombre), Order by Expression ('Ventas_Campaña_Google'[Descripción]).

* My goal is to get the names of the clients related to the table (one-to-many relationship) to return all the services performed by said clients.

Thx a lot!! 

[tamerj1]

Which one is the many side? What is the code for the measure [Nombre]?

[4pepesanchez4]

The field “Clienta” (severals) and field “Name” (single) is the active relationship between tables.

In the table of the "Clienta" field I have all the services that the clients have performed in the month (each service is a line so there are repeated lines) and in the second table the field "Name" are the clients that have come through from Google so it is a unique field.

 I do not have the "Name" field defined as any measure. It is simply the unique name of each client and what I want it to return is the "Description" field of the table (Ventas_Camapaña_Google) to know what services those clients have performed.

[tamerj1 · Accepted solution]

Hi @4pepesanchez4 
Please use this

Cities = 
CONCATENATEX (
    Ventas_Compana_Google,
    Ventas_Compana_Google[Descripcion],
    " | "
)

[4pepesanchez4]

What if I had to do it in Excel without DAX formulas? 

Thx a lot!

[tamerj1]

@4pepesanchez4  😅
Yes why not. It is for sure the easiest way.

[4pepesanchez4]

I'm sorry for my level of English. What I wanted to say is that the definition proposal is through a DAX formula (CalculateX) and I'm using Excel, which doesn't give me the option to use that formula, right? Wouldn't there be another way to do the exercise without DAX formulas?

Thx

[tamerj1]

@4pepesanchez4 
It is CONCATENATEX not CALCULATEX
and it is available in Excel's Power Pivot. It should work normally with no issues.

[tamerj1 · Accepted solution]

Hi @4pepesanchez4 
You can use a table or matrix visual

Measure 

Cities = 
CONCATENATEX (
    Countries,
    Countries[city],
    " | "
)