Forum Discussion

PowerBIUser9901's avatar
PowerBIUser9901
Advocate II
6 years ago
Solved

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

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!

Need Help

7 Replies

    • PowerBIUser9901's avatar
      PowerBIUser9901
      Advocate II
      6 years ago

      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's avatar
        camargos88
        Community Champion
        6 years ago

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