Forum Discussion
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...
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], "," )
Really nice!!!
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:
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],
","
)