Dynamically return an associated value from my dimension table to my data table

[PowerBIUser9901]

Hi, I need guidance in how I can dynamically return an associated value from my dimension table to my data table. (Data file included in link below).

In the screenshot below you’ll notice that the data table Region column contains both countries and regions. I’m trying to take the regions (highlighted in green) such as “LATAM” and “U.S – All States” and instead return the associated countries/states in a list view (such as in the Desired Output table).

The associations should dynamically update from the dimension table as the dimension table can be updated to include additional countries. (Dimenstion Table Screenshot Below)

I also have the ability to change my dimension table in any way such changing and or adding column fields in order to make this happen.

The data file can be downloaded in the link below:

https://1drv.ms/x/s!AltxA49hBwkYgetjYs-nNNezkuaRPw?e=dQp0fh

I appreciate any help you can provide. Thank you!

[camargos88 · Accepted solution]

Hi @PowerBIUser9901 ,

Chech this file: Download PBIX 

I've made some changes regards to the group names, once you are able to do it.

[PowerBIUser9901]

Wow! This is exactly what I'm looking for, Thank you! Could you please explain the m code logic in order to make this happen. I may try to do this for similar cases. 

[camargos88]

Hi @PowerBIUser9901 ,

I'm glad it worked.

The function looks for rows with  [Region Type] = "Country/Territory" and some match on [DataTable Value] / [Assosiated Region] / [Location] using the _region variable defined before (it is necessary to compare between tables).

It returns a list of [DataTable Value] and combines using the function Text.Combine.

Be aware that was required to apply a trim function on the columns to remove the spaces (begin and end).

This is the code:

let _region = [Region] in
Text.Combine(Table.SelectRows(DimensionTable,
each [Region Type] = "Country/Territory"
and (
[DataTable Value] = _region or
[Assosiated Region] = _region or
[Location] = _region
)
) [DataTable Value], ", ")

[PowerBIUser9901]

Thank you so much for the explanation. I noticed that you changed the DataTable row values text. How would you apply this logic if you can only change the Dim table however not change the row values orignal text in the Data Table.

Example: "International - All" can not be changed in the DataTable to "International", however you do have complete control over the Dimension Table.

[camargos88]

@PowerBIUser9901 ,

If I got your point correctly... 

You should have all the correspondencies on the Region Dimension Table, specially for the grouped values like International - All, Europe and US.

Can you change the value "Internacional" to "International - All" on Region Dimension Table ? And do it with other values like Europe to EU - All and U.S. State/Territory to U.S. - All States ?

If no, let me know to create another solution.

[PowerBIUser9901]

Hi Ricardo,

I do have the ability to modify the dimension table. When I tried your suggested modifications, I ended up getting blanks for some of the countries/regions. Could you provide an example of how you would create the list via the custom column though not change the data table Region values? I appreciate any guidance you can provide.

[camargos88]

Hi @PowerBIUser9901 ,

Check the file again: Download PBIX 

I changed the [Assosiated Region] for the grouped values to:

and some hard code: