Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
7 years ago
Solved

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,

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

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

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Iamnvt 

     

    Hi

     

    Region_ =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Table2[Region] ),
        Table2[Product] = EARLIER ( Table1[Product] )
    ),
    [Region],
    ","
)
    • Iamnvt's avatar
      Iamnvt
      Continued Contributor
      7 years ago

      Thanks very much

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