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

[RashmiShivanna]

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. 

[Stachu]

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/

[RashmiShivanna]

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   

[Stachu]

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

[RashmiShivanna]

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

But if I include a measure, it works. 

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)

[Stachu]

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