The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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.
thank you everyone. It resolved in back. 😀
@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.
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.
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:
Steps to Implement:
Result:
City |
Text_Value |
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. |
Hope this helps.
Thanks!
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.
Hi @Peter_23 -Instead of retrieving only the MAX value, we can concatenate all the text values for each city using the CONCATENATEX function.
Proud to be a Super User! | |