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 A AA A BB B BB C AA   I need to write a calculated column that lookupvalue all the Region from...
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],
    ","
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

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's avatar
Iamnvt
Continued Contributor
7 years ago

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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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],
    ","
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

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