cancel
Showing results for
Did you mean:
Iamnvt Member

## Concatenatex Lookupvalue multiple values

hi,

I have 2 tables:

Product

 A B C

and Sales:

ProductRegion

 A AA A BB B BB C AA

I need to write a calculated column that lookupvalue all the Region from Sales Table, and concatenatex the values by "," delimeter.

Expected Result:

 Product Region A AA, BB B BB C CC

lookupvalues has error:

`Region_ = LOOKUPVALUE(Table2[Region],Table2[Product],Table1[Product])`

`Expression has the following warning: A table of multiple values was supplied where a single value was expected.`

PBI file is here:

https://1drv.ms/u/s!Aps8poidQa5zk6sHt7Go6e8D7qRPUw

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions Super User III

## Re: Concatenatex Lookupvalue multiple values

You can use CROSSFILTER function to get this column in Table1, if you have the aforesaid relationships

```Country =
CONCATENATEX (
CALCULATETABLE (
VALUES ( Table3[Country] ),
CROSSFILTER ( Table2[Region], Table3[Region], BOTH )
),
[Country],
","
)
```
Try my new Power BI game Cross the River
7 REPLIES 7 Super User III

## Re: Concatenatex Lookupvalue multiple values

Hi

```Region_ =
CONCATENATEX (
CALCULATETABLE (
VALUES ( Table2[Region] ),
Table2[Product] = EARLIER ( Table1[Product] )
),
[Region],
","
)
```
Try my new Power BI game Cross the River
Iamnvt Member

## Re: Concatenatex Lookupvalue multiple values

Thanks very much

@Zubair_Muhammad  is there any ways that I can concatenatex the lookupvalue formula?

Iamnvt Member

## Re: Concatenatex Lookupvalue multiple values

what if I don't have physical relationship between the 2 tables; how can I do that? Super User III

## Re: Concatenatex Lookupvalue multiple values

@Iamnvt

Actually above formula was for the case when there is no relationship.

It is a substitute for Lookupvalue

In case there is a relationship, it becomes simpler

```Region_ =
CONCATENATEX ( CALCULATETABLE ( VALUES ( Table2[Region] ) ), [Region], "," )
```

Try my new Power BI game Cross the River
Iamnvt Member

## Re: Concatenatex Lookupvalue multiple values

Really nice!!!

I have a bit more complex scenarios. I have another Table 3, that has the mapping between Region, and Country:

RegionCountry

 AA a BB b

How can I transverse the Country from Table 3 to Table 1 directly?

I know that I can transverse from Table 3 to Table 2 first, then Table 2 to Table 1. Just curious to know if any better way.

Expected Results:

 Product Region Country A AA, BB a, b B BB b C CC

PBI file here:

https://1drv.ms/u/s!Aps8poidQa5zk6sHt7Go6e8D7qRPUw Super User III

## Re: Concatenatex Lookupvalue multiple values

You can use CROSSFILTER function to get this column in Table1, if you have the aforesaid relationships

```Country =
CONCATENATEX (
CALCULATETABLE (
VALUES ( Table3[Country] ),
CROSSFILTER ( Table2[Region], Table3[Region], BOTH )
),
[Country],
","
)
```
Try my new Power BI game Cross the River Super User III

## Re: Concatenatex Lookupvalue multiple values

Another way could be to use TREATAS

```Country2 =
CONCATENATEX (
CALCULATETABLE (
VALUES ( Table3[Country] ),
TREATAS ( CALCULATETABLE ( VALUES ( Table2[Region] ) ), Table3[Region] )
),
[Country],
","
)
```

or to use INTERSECT function

```Country 3 =
CONCATENATEX (
CALCULATETABLE (
VALUES ( Table3[Country] ),
INTERSECT (
VALUES ( Table3[Region] ),
CALCULATETABLE ( VALUES ( Table2[Region] ) )
)
),
[Country],
","
)
```
Try my new Power BI game Cross the River

Announcements #### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest! #### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system. #### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge. #### Power Platform Online Conference 