cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iamnvt Member
Member

Concatenatex Lookupvalue multiple values

hi,

 

I have 2 tables:

Product

A
B
C

 

and Sales:

ProductRegion

AAA
ABB
BBB
CAA

 

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

 

Expected Result:

ProductRegion
AAA, BB
BBB
CCC

 

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
Super User III

Re: Concatenatex Lookupvalue multiple values

@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],
    ","
)
Try my new Power BI game Cross the River

View solution in original post

7 REPLIES 7
Super User III
Super User III

Re: Concatenatex Lookupvalue multiple values

@Iamnvt 

 

Hi

 

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

Re: Concatenatex Lookupvalue multiple values

Thanks very much

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

Iamnvt Member
Member

Re: Concatenatex Lookupvalue multiple values

@Zubair_Muhammad 

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

Super User III
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
Member

Re: Concatenatex Lookupvalue multiple values

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

AAa
BBb

 

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:

ProductRegionCountry
AAA, BBa, b
BBBb
CCC 

 

PBI file here:

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

Super User III
Super User III

Re: Concatenatex Lookupvalue multiple values

@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],
    ","
)
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Concatenatex Lookupvalue multiple values

@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],
    ","
)
Try my new Power BI game Cross the River

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors