Create text measure and relationship with two tables

[Peter_23]

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

Table A

Country	State	City	value
US	CA	LA	1250
Brazil	Amazonas	Manaos	3468

Table B

Country	State	City	Text
US	CA	LA	It is the most populous city in the U.S. state of California.
US	CA	LA	Los Angeles has a diverse economy with a broad range of industries.
Brazil	Amazonas	Manaos	It 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.

[Peter_23]

thank you everyone. It resolved in back. 😀

[Peter_23]

@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. 

[v-rzhou-msft · Accepted solution]

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:

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"

Create a relationship between [KeyWords] columns.

Result is as below.

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]

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!

[rohit1991]

Here’s a concise solution for your Power BI issue using relationships. Create a composite key in both tables by concatenating the Country, State, and City columns. You can do this using a calculated column:

a) In Table A: Key = TableA[Country] & "_" & TableA[State] & "_" & TableA[City] 

b) In Table B: Key = TableB[Country] & "_" & TableB[State] & "_" & TableB[City] 

c) Establish a 1-to-Many relationship between Table A[Key] (one) and Table B[Key] (many).

or

Create a Measure for Text:

If you want to avoid a physical relationship, you can use a measure to create a virtual relationship between the tables.Here’s how you can write the measure Text_Value:

 Text_Value = CONCATENATEX( FILTER( TableB, TableB[Country] = SELECTEDVALUE(TableA[Country]) && TableB[State] = SELECTEDVALUE(TableA[State]) && TableB[City] = SELECTEDVALUE(TableA[City]) ), TableB[Text], UNICHAR(10) -- Adds a line break for multiple rows ) 

This measure retrieves all the text values from Table B that match the current context of Country, State, and City from Table A. It also concatenates multiple rows with line breaks.

[rajendraongole1]

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

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.