Relation not working in measure with text operation

[ROYMMM]

Hi,

I have two tables. For the sake of privacy and simplicity I have here some fake data. It's a ticket table and a client table. I want to strip off part of the ticket summary and the the name of the client who logged the ticket. In the example below I want to strip off the "blabla" part of the summary. A calculated column is NOT an option as I use an existing published dataset. An active relation exists between Client  ID from the Ticket table and the Client ID from the Client table.

Ticket table

Ticket number	Summary	Client ID
10000	A blabla	1
10001	B blabla	2
10002	C blabla	3
10003	D blabla	4

Client table

Client ID	Client Name
1	John
2	Robert
3	Jim
4	Sally

So I thought the following measure would work:

Short_Summary=IF(LEFT(MIN(Ticket[Summary]),1)="A","A",

                                IF(LEFT(MIN(Ticket[Summary]),1)="B","B",

                                    IF(LEFT(MIN(Ticket[Summary]),1)="C","C",

                                       IF(LEFT(MIN(Ticket[Summary]),1)="D","D","other"))))

Expected outcome:

Ticket Number	Short Summary	Client Name
10000	A	John
10001	B	Robert
10002	C	Jim
10003	D	Sally

Actual outcome:

Ticket number	Short Summary	Client Name
10000	A	John
10000	A	Robert
10000	A	Jim
10000	A	Sally
10001	B	John
10001	B	Robert
10001	B	Jim
10001	B	Sally
10002	C	John
10002	C	Robert
10002	C	Jim
10002	C	Sally
10003	D	John
10003	D	Robert
10003	D	Jim
10003	D	Sally

Can someone help me with getting this measure working? 

Thanks.

[v-eqin-msft · Accepted solution]

Hi @ROYMMM ,

If the relationship between the tables is 1*Many as shown below:

Please try  to use the following formula to combine all distinct Name of each Ticket number:

Measure = 
var _t1=ADDCOLUMNS('Ticket',"Name",LOOKUPVALUE(Client[Client Name],'Client'[Client ID ],[Client ID]))
var _t= SUMMARIZE(_t1,[Ticket number],[Name])
return CONCATENATEX(_t,[Name],",")

Output:

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

[johnt75]

Try replacing all the MINs with SELECTEDVALUEs

[hashtag_pete]

Hey, 

no, not in a measure, but in a newly created table. Further measure can then rely on this new table. 

But it's hard to say if this works for you with the information provided...

[ROYMMM]

The problem is that adding calculated tables to composite models result in an issue that refreshes in Power BI Service fail. So I only can use measures. Is there no other way?

[hashtag_pete]

Hmm, not quite sure why it fails (it shouldn't). You can refer to the MS guidance on how to set this up properly:

Composite model guidance in Power BI Desktop - Power BI | Microsoft Docs

[ROYMMM]

Thanks for your quick response. I tried to use RELATED already, but I can't use it in a measure. I also tried RELATEDTABLE, but that doesn't work either.

[hashtag_pete]

Hello @ROYMMM 

if an existing relationship is in place, use RELATED and return the column you want. You can wrap a left function around it for getting the A, B, C, D

Best

hashtag_pete