cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Relation not working in measure with text operation

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.

1 ACCEPTED SOLUTION
Community Support

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.

7 REPLIES 7
Community Support

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.

Super User

Try replacing all the MINs with SELECTEDVALUEs

Helper V

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

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?

Helper V

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

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.

Helper V

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