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

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

Reply
ROYMMM
Advocate I
Advocate I

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 numberSummaryClient ID
10000A blabla1
10001B blabla2
10002C blabla3
10003D blabla4

 

Client table

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

 

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 SummaryClient Name 
10000AJohn
10001BRobert
10002CJim
10003DSally

 

Actual outcome:

Ticket numberShort SummaryClient Name
10000AJohn
10000ARobert
10000AJim
10000ASally
10001BJohn
10001BRobert
10001BJim
10001BSally
10002CJohn
10002CRobert
10002CJim
10002CSally
10003DJohn
10003DRobert
10003DJim
10003DSally

 

Can someone help me with getting this measure working? 

 

Thanks.

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @ROYMMM ,

 

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

Eyelyn9_0-1648439865268.png

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:

Eyelyn9_1-1648439985061.png

 

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.

View solution in original post

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

Hi @ROYMMM ,

 

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

Eyelyn9_0-1648439865268.png

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:

Eyelyn9_1-1648439985061.png

 

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
Super User
Super User

Try replacing all the MINs with SELECTEDVALUEs

hashtag_pete
Helper V
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?

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
Advocate I
Advocate I

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
Helper V
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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