cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saranp780 Member
Member

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

I have some data a table below.

 

UsernameRoleMarkPlant
ADM1303Mangement1303x1303
PUR1303Procurement1303x13031401 
ADM1303ADMIN1303x1303
 ACT1403  
PUR1303Procurement1401x1303 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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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],",")

 Capture1.PNG

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

 

Regards,

Jimmy Tao

2 REPLIES 2
Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

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

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],",")

 Capture1.PNG

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

 

Regards,

Jimmy Tao