Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Peter_23
Helper V
Helper V

Create text measure and relationship with two tables

Hi community, in this time,  I try to figure out how to development this issue.

 

Table A

CountryStateCityvalue
USCALA1250
BrazilAmazonasManaos

3468

 

Table B

CountryStateCityText
USCALAIt is the most populous city in the U.S. state of California.
USCALALos Angeles has a diverse economy with a broad range of industries.
BrazilAmazonasManaosIt is the capital and largest city of the Brazilian state of Amazonas.

 

Many values  duplicate exists in Country, state, city in both tables.

I don't have any relationship between two tables.

 

In the report I have country, state, city from Table A as filters, what  I need a matrix as:

 

City
LA
Los Angeles has a diverse economy with a broad range of industries
It is the most populous city in the U.S. state of California.
Manaos
It is the capital and largest city of the Brazilian state of Amazonas.

 

 

I created a measure "Text_value"

 

CALCULATE(
    MAX(A[Text]),
    TREATAS(
        VALUES(A[City]),
        B[City]
    )
)

 

 

but the table as result with max value of each city (category) 🙄, and sometimes filters aren't work! I'm trying to work with virtual relationship, but if it is neccesary a physical relationship  I will create.

 

This is I'm having..

 

City
LA
Los Angeles has a diverse economy with a broad range of industries
Manaos
It is the capital and largest city of the Brazilian state of Amazonas.

 

Thanks in advance.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Peter_23 ,

 

You can add an index column based on [Country] & [State]&[City] in B table.

Then add a keyword column to group [Country] & [State]&[City] column in both tables.

A:

vrzhoumsft_0-1737357476789.png

B:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9BCsJQDESvMnQtvUN1JehKXImL2EYb+E3KT6ro6f1VRMTNEDLhZeZwqPa7alGtmiKbWdYBcUTPGMwDo41TssnRStwh+nL29a6GBwXDzlhRkrNlFaqr4+IPuDFHoxdO7OjJQejkytkZ3JracMdNoi/rUzbqkKnczljRbvLIwv7GLjM9JBVgM9DDlLyMW1Iy/0nd0ihBCaQdEuULlxKv7AU5+2+MkH4LfIDlz/EJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, State = _t, City = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"State", type text}, {"City", type text}, {"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "KeyWords", each [Country]&"_"&[State]&"_"&[City]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Country", "State", "City", "KeyWords"}, {{"Rows", each _, type table [Country=nullable text, State=nullable text, City=nullable text, Text=nullable text, KeyWords=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Text", "Index"}, {"Text", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"})
in
    #"Removed Columns"

vrzhoumsft_1-1737357488374.png

Create a relationship between [KeyWords] columns.

vrzhoumsft_2-1737357541262.png

Result is as below.

vrzhoumsft_4-1737357585807.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
Peter_23
Helper V
Helper V

thank you everyone. It resolved in back. 😀

Peter_23
Helper V
Helper V

@SamInogic  @rohit1991  @rajendraongole1  thanks for response, I trying to use concatenex, but the visual, it's no responding I have to many values (1000+ records), and the filters, it's multiple selections.

 

😞

 

any option to try?

 

thanks in advance. 

Anonymous
Not applicable

Hi @Peter_23 ,

 

You can add an index column based on [Country] & [State]&[City] in B table.

Then add a keyword column to group [Country] & [State]&[City] column in both tables.

A:

vrzhoumsft_0-1737357476789.png

B:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9BCsJQDESvMnQtvUN1JehKXImL2EYb+E3KT6ro6f1VRMTNEDLhZeZwqPa7alGtmiKbWdYBcUTPGMwDo41TssnRStwh+nL29a6GBwXDzlhRkrNlFaqr4+IPuDFHoxdO7OjJQejkytkZ3JracMdNoi/rUzbqkKnczljRbvLIwv7GLjM9JBVgM9DDlLyMW1Iy/0nd0ihBCaQdEuULlxKv7AU5+2+MkH4LfIDlz/EJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, State = _t, City = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"State", type text}, {"City", type text}, {"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "KeyWords", each [Country]&"_"&[State]&"_"&[City]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Country", "State", "City", "KeyWords"}, {{"Rows", each _, type table [Country=nullable text, State=nullable text, City=nullable text, Text=nullable text, KeyWords=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Text", "Index"}, {"Text", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"})
in
    #"Removed Columns"

vrzhoumsft_1-1737357488374.png

Create a relationship between [KeyWords] columns.

vrzhoumsft_2-1737357541262.png

Result is as below.

vrzhoumsft_4-1737357585807.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

SamInogic
Super User
Super User

Hi,

 

As per our understanding you’re trying  to achieve the desired matrix visualization in Power BI without creating a physical relationship between Table A and Table B, you can adjust your measure to correctly handle multiple text entries per city. The issue with using MAX is that it only returns one value, not all relevant text entries.

Solution: Concatenate Text Values Using a Measure

Use the CONCATENATEX function to list all related text entries.

Updated Measure:

Text_Value =

CALCULATE(

    CONCATENATEX(

        FILTER(

            B,

            B[Country] = SELECTEDVALUE(A[Country]) &&

            B[State] = SELECTEDVALUE(A[State]) &&

            B[City] = SELECTEDVALUE(A[City])

        ),

        B[Text],

        UNICHAR(10)

    )

)

Explanation:

  • FILTER: Filters Table B to match the selected Country, State, and City from Table A.
  • CONCATENATEX: Concatenates all the matching text entries in B[Text], separated by a line break (UNICHAR(10)).
  • SELECTEDVALUE: Captures the selected filter value for Country, State, and City.

 

Steps to Implement:

  1. Create the Measure: Add the updated Text_Value measure to Table A.
  2. Matrix Visualization:
    • Rows: City (from Table A)
    • Values: Text_Value (this measure)
  3. Filters: Use Country, State, and City from Table A in the slicers.

 

Result:

City

Text_Value

LA

Los Angeles has a diverse economy with a broad range of industries.
It is the most populous city in the U.S. state of California.

Manaos

It is the capital and largest city of the Brazilian state of Amazonas.


Hope this helps.

 

Thanks!

 

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
rohit1991
Super User
Super User

Hey @Peter_23,

You’re on the right track. There are two solid ways to achieve this in Power BI, depending on how you want to structure your model:

Option 1: Composite Key & Relationship (Best for large models & dynamic filtering)

  • In both Table A and Table B, create a new calculated column called Key like this:
    Key = [Country] & "-" & [State] & "-" & [City]

  • Then, set up a 1-to-many relationship from Table A [Key] to Table B [Key].

  • Now, you can use RELATED or LOOKUPVALUE in DAX if you want a column, or just pull values in visuals using the relationship.

Option 2: DAX Measure Using CONCATENATEX (Best for flexibility, no model changes needed)
If you don’t want to set up a relationship, you can use a measure to do a “virtual join” and pull all text values for each city:

Text_Value = 
CONCATENATEX(
    FILTER(
        TableB,
        TableB[Country] = SELECTEDVALUE(TableA[Country]) &&
        TableB[State] = SELECTEDVALUE(TableA[State]) &&
        TableB[City] = SELECTEDVALUE(TableA[City])
    ),
    TableB[Text],
    UNICHAR(10) // line break
)

 

This measure will show all the matching text entries (with line breaks) in your matrix, even if you have multiple per city. If your model supports it and you want robust filtering, go with the composite key & relationship. If you just want a quick solution or can’t change your data model, use the DAX measure.

 

If you have a huge dataset, the composite key approach will scale better. For smaller or ad-hoc setups, the DAX measure is usually all you need.

 


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
rajendraongole1
Super User
Super User

Hi @Peter_23 -Instead of retrieving only the MAX value, we can concatenate all the text values for each city using the CONCATENATEX function.

 

rajendraongole1_1-1737090148456.png

Text_Values =
CALCULATE(
    CONCATENATEX(
        _Tab2,  -- Ta_Tab2le _Tab2
        _Tab2[Text],  -- Text column from Ta_Tab2le _Tab2
        UNICHAR(10),  -- Separator (line _Tab2reak)
        _Tab2[Text],  -- Sorting order (can _Tab2e changed if needed)
        ASC  -- Ascending order
    ),
    TREATAS(
        VALUES(_Tab1[City]),  -- City from Ta_Tab2le A
        _Tab2[City]  -- City from Table B
    )
)
 
Please check the above logic.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors