Concatenatex Lookupvalue multiple values

[Iamnvt]

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,

[Zubair_Muhammad]

@Iamnvt 

Hi

Region_ =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Table2[Region] ),
        Table2[Product] = EARLIER ( Table1[Product] )
    ),
    [Region],
    ","
)

[Iamnvt]

Thanks very much

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

[Iamnvt]

@Zubair_Muhammad 

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

[Zubair_Muhammad]

@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], "," )

[Iamnvt]

Really nice!!! 

@Zubair_Muhammad 

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

[Zubair_Muhammad]

@Iamnvt 

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],
    ","
)

[Zubair_Muhammad · Accepted solution]

@Iamnvt 

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],
    ","
)