Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |