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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RashmiShivanna
Frequent Visitor

Join tables using only text columns in a N:1:N relationship

We have a requirement to join tables which are connected by a N:1:N relationships. So, when we try to join the tables by dragging the text columns, it throws the error "power bi can't determine relationships between the fields". It works if we include a measure in the same visualization. To make this work, I am trying to convert the text columns to measure but as far as I know, a measure can only return one value. So, when we convert the text columns to measure, I am only seeing one value unless I include an ID column. We want to be able to drag text column alone and see all the values in it and also join it with another text column and be able to see the join results and not just one row

 

I have tried the following DAX expression to create measures out of text columns - 

Comment Text:= FIRSTNONBLANK(Comments[Text],1) ---- Displays only one row unless it is joined with an ID column

CommentedBy_3 = CALCULATE(VALUES(Comments[CommentedBy]), USERELATIONSHIP(Comments[ClientId], Clients[ClientId_])) ----- Works only if the values are filtered in a slicer

 

 

I am aware of the composite models released just now. This completely resolves the issue. But it has its own drawbacks, 1. We use SSAS and composite models are not yet designed for ssas. 2. We can't publish the reports containing composite models 

 

Please let me know if there is any way we can work around this issue - Join tables involved in N:1:N reltionships using text columns only. 

 

5 REPLIES 5
Stachu
Community Champion
Community Champion

not sure I get the requirement right, but I assume you want to see all the comments related to a customer, is that correct?
Measure can only return scalar value, so you can use CONCATENATEX to transform multiple comments into string, UNICHAR(10) is a line break so it would look better visually

 

as for joining the 2 tables, the INTERSECT pattern from SQLBI can do that

[Filtered Measure] := 
CALCULATE (
    <target_measure>,
    INTERSECT (
        ALL ( <target_granularity_column> ), 
        VALUES ( <lookup_granularity_column> )
    )
)

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi Stachu, Thanks for the response.  We are using SSAS tabular model as the data source and it is a live connection. So, unichar is not supported. I still tested it and it is throwing an error saying "Failed to resolve name 'unichar'". I found this link https://community.powerbi.com/t5/Desktop/UNICHAR-DAX-function-does-not-exist-in-Power-BI-Desktop/td-... which has information on this. unichar is not supported in ssas live connection unless compatibility level is 1400 and above. Our model is 1200

 

The intersect option you suggested, I am not sure what should be the taregt_measure if I am specifying text columns in the intersect   

you still could use the pattern and have more common delimiter for comments such as ";"
can you paste here few sample rows of the Comments and Clients tables?
based on the syntax you posted something like this may work

Measure = 
CALCULATE(
    CONCATENATEX(Comments,Comments[CommentedBy],";"),
    INTERSECT(ALL(Clients[ClientId_]),VALUES(Comments[ClientId]))
)

EDIT - this would fix duplicates in the commented by

Measure = 
CALCULATE(
    CONCATENATEX(DISTINCT(Comments[CommentedBy]),Comments[CommentedBy],";"),
    INTERSECT(ALL(Clients[ClientId_]),VALUES(Comments[ClientId]))
)


 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

 

As you can see below, when we create a table visualization with text columns from Comments table and CLientQAs, it throws error. 

 

Screenshot_1.png

 

But if I include a measure, it works. 

measure.png

 

This is an issue because the tables are related by N:1:N relationship. So to fix this, I was looking for a way to convert text columns into measures so that instead of original text columns we can use the measure. 

 

So, if I use concatenate with a delimiter other than new line, they will just be a string and not rows in a table. we need to be able to look at the values in text column(converted to measure) and also be able to use it to join with other text columns like below. Commented_By is a text column in Comments table and Answer is a text column from ClientQAs table. we want to be able to out them into same table without using a 3rd column(measure) and also, look at the values of those text columns individually(like how we see commented_by in a separate table)

 

both.png

if the tables are effectively in many to many relationship PowerBI will try to create cartesian product of the 2, which is why you get the error
you cannot use the measure so it would act as a column, but I think you can write DAX that would filter the cartesian product of the 2 tables only to something meaningful

what you want to see is all the answers for a given customer and who commented on them? in your screenshot Answers field is empty, so I cannot understand the business logic behind the request



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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