topic Re: search for a value and return multiple fields in DAX Commands and Tips

search for a value and return multiple fields

4pepesanchez4 — Fri, 20 May 2022 07:12:06 GMT

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.

Re: search for a value and return multiple fields

tamerj1 — Fri, 20 May 2022 08:15:52 GMT

Hi 4pepesanchez4
You can use a table or matrix visual

Measure

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

Re: search for a value and return multiple fields

4pepesanchez4 — Fri, 20 May 2022 08:57:12 GMT

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

Thx a lot!

Re: search for a value and return multiple fields

tamerj1 — Fri, 20 May 2022 09:03:42 GMT

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

Re: search for a value and return multiple fields

4pepesanchez4 — Fri, 20 May 2022 09:08:13 GMT

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

Re: search for a value and return multiple fields

tamerj1 — Fri, 20 May 2022 09:17:10 GMT

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

Re: search for a value and return multiple fields

4pepesanchez4 — Sun, 22 May 2022 10:59:31 GMT

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!!

Re: search for a value and return multiple fields

tamerj1 — Sun, 22 May 2022 15:16:06 GMT

Hi 4pepesanchez4

just change the table name to the cities table

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

Re: search for a value and return multiple fields

4pepesanchez4 — Mon, 23 May 2022 07:46:24 GMT

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!!

Re: search for a value and return multiple fields

tamerj1 — Mon, 23 May 2022 08:22:28 GMT

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

Re: search for a value and return multiple fields

4pepesanchez4 — Mon, 23 May 2022 08:41:18 GMT

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.

Re: search for a value and return multiple fields

tamerj1 — Mon, 23 May 2022 09:56:02 GMT

Hi 4pepesanchez4
Please use this

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