Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a table with customer ID and postcode they live in (table A) and another table with postcodes and regions (Table B). I would like to create a column in table A with the Region related to the postcode.
i have tried the merge function but it looks like the final table is not right! Also playing with a lookupvalue function, however I am getting the "a table if multiple values was supplied where a single value was expected" error
Any help would be greatly appreciated
Solved! Go to Solution.
Hi @Anonymous ,
If the postcode column has unique vaule in the table B, the lookupvalue should work fine as following calculate column
Region = LOOKUPVALUE('Table B'[regions],'Table B'[postcode],[postcode])
If one postcode has multi region, you can give me the expected result based on my sample data.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If the postcode column has unique vaule in the table B, the lookupvalue should work fine as following calculate column
Region = LOOKUPVALUE('Table B'[regions],'Table B'[postcode],[postcode])
If one postcode has multi region, you can give me the expected result based on my sample data.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You will need to join the two tables using postcode field and use below dax to create a calculated column in Table A to get the region field from Table B.
Region = RELATED( Table B[Region] )
Thanks.
Tried it, but I am getting an error saying that the region field is not recognised as there is no relationship between the two tables, even thoug hI created the relationship already.
Is there anything iam missing?
Hi,
Can you share your report pbix?
Thanks.
User | Count |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |