Can't reference a table in my SUBSTITUTE formula...

[Anonymous]

Hey folks, probably a simple fix but something I'm stuck on:

I'm trying to create a SUBSTITUTE function to replace some text within a string to generate a URL for users to click. Here's my code thus far

Measure 1 = 

SUBSTITUTE( 
	SUBSTITUTE(
		SUBSTITUTE(
			SUBSTITUTE( (CP), "(", "")
		,")",""),
	"/",""),
" ","-")

The issue I'm having is during the last SUBSTITUTE argument where you see the (CP) listed. CP is one of the sheets in my dataset, however it's not the one I need to reference. I'm trying to reference a different sheet and column, but when I type the sheet/table name in, nothing is found. The only data PBI can find is from the CP sheet. 

If I try a different argument, say SUM, then I can find the sheet/table I'm trying to reference in the SUBSTITUTE argument.

I'm not sure what gives? Why can I see it in some arguments (SUM) but not others (SUBSTITUTE)?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous,

Acutally, you can create a calculated column on the table you want  to refer so that you refer the fields of that table. But if you insist on using measure to get the results, you can refer to fields in that tables with the help of some functions. For example : FIRSTNONBLANK (suggested by @Anonymous ) , MAX , MIN or other function . Hence , you can update your measure formula as below : 

Best Regards

Rena

[Anonymous]

Hi @Anonymous 

It looks like you have a calculated measure, if you use a calculated column then you will be able to reference the column you want. The calculated column will have to be in the same table. If you have to use a measure, you need to use an expression that will reference the column such as "firstnonblank", etc.

- Paul