Hi,
I've got quite a unique scenario over here involving RLS implementation.
If you take a look at the sample data, it's the case that for individual users e.g. user1, I have corresponding manager information and I'm able to calculate the heirarchy in Path column using DAX PATH() function.
The problem lies with the rows where essentially users are merged in form of teams e.g. "user1/user2" and they may have different managers. As a starter, I created Calculated Username to get usernames from individual users (how? long story.)
Now, I need to create a column as portrayed in Calculated Path.
Sample Data:
| User | Username | Calculated Username | Path | Calculated Path (Desired column) | |
| user1 | user1@company.com | user1@company.com | Manager1 | user1 | Manager1 | user1 | |
| user2 | user2@company.com | user2@company.com | Manager2 | user2 | Manager2 | user2 | |
| user1/user2 | null | user1@company.com;user2@company.com | null | Manager1 | user1; Manager 2 | user2 | |
Furthermore, I found this code by @TomMartens from https://community.powerbi.com/t5/Desktop/DAX-forumula-help-with-comma-separated-values/m-p/534837
//Awesome job on the code btw.
Column =
var stringSelected = FIRSTNONBLANK('Consolidated Sales Rep Table'[Calculated Username],0)
var stringAsPath = SUBSTITUTE(stringSelected,";","|")
var intPathLength = PATHLENGTH(stringAsPath)+0
var tempTable =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,intPathLength)
,"Users",PATHITEM(stringAsPath,''[Value],TEXT)
)
,"Users", [Users]
)
return
CONCATENATEX(temp,[New Path],"|")
What this code does is, is that it separates delimeter based strings and creates a temporary table and stores individual strings in rows, pretty neat stuff!
How I am trying to leverage it:
By adding one more variable to the code:
var PleaseDearGod = FILTER('myTable', 'myTable'[Username] IN {tempTable})
return CONCATENATEX( PleaseDearGod, Path], "|" )
// It would check "user1@company.com" & "user2@company.com" (in form of two rows) and filter the table where they have their individual user data and fetch Path column.
The issue with this approach seems to be the splitting of Calculated Username into two rows, which generates "A table of multiple values was supplied error where a single value was expected."
Could you please help me out in this? I'd highly appreciate it.
@TomMartens @parry2k @Greg_Deckler @PhilipTreacy
