Can anybody lets me know how to find some letters in Power BI?

[saranp780]

I have some data a table below.

Username	Role	Mark	Plant
ADM1303	Mangement1303	x	1303
PUR1303	Procurement1303	x	13031401
ADM1303	ADMIN1303	x	1303
	ACT1403
PUR1303	Procurement1401	x	1303 1401
	Procurement1404

If I got data from a user "PUR1303", the result would be 1303,1401.
I had a Plant no. at 4 digits number after "Role". Such as at row1 I had a username "ADM1303" with the roles "Managemen1303"[Plamt 1303] and ADMIN 1303" and I got "X" value at [Mark] so I want to get a plant no. from"Role". This result of the plant column would be 1303.

[v-yuta-msft · Accepted solution]

Hi saranp780,

You can use DAX formula below:

Measure = CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(ALL(Table1),[Mark]="x" && Table1[Username]=MAX('Table1'[Username])),"Plant",RIGHT([Role],4))),[Plant],",")

 

PBIX here: https://www.dropbox.com/s/7izzb5clb19z4hg/testsamle.pbix?dl=0

Regards,

Jimmy Tao

[Greg_Deckler]

I'm not 100% sure of what you are asking but you could use the RIGHT function to grab the last four letters of a string like this:

Column = RIGHT([SomeColumn],4)

If you want it as an actual number, just wrap your RIGHT function in VALUE.