Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Sales Table, and concatenatex the values by "," delimeter.
Expected Result:
Product | Region |
A | AA, BB |
B | BB |
C | CC |
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,
Solved! Go to Solution.
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], "," )
Hi
Region_ = CONCATENATEX ( CALCULATETABLE ( VALUES ( Table2[Region] ), Table2[Product] = EARLIER ( Table1[Product] ) ), [Region], "," )
Thanks very much
@Zubair_Muhammad is there any ways that I can concatenatex the lookupvalue formula?
what if I don't have physical relationship between the 2 tables; how can I do that?
@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], "," )
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], "," )
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], "," )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |