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

Zubair_Muhammad 

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

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