I need to create a Slice based on the USEREMAIL() to verify either he has a right to view particular country data.
This is the table, I need to search.
AND
(
[IsPosted]=TRUE,
IN(USEREMAIL(), SELECT(Tbl_Businessunits[RepresentativeEmailID], [BusinessUnit]=[_THISROW].[xBusinessUnit])))
The above condition does not work as expected.
Any idea please.
Thanks in advance.
John[quote=โJohn_Henry_Joseph, post:1, topic:34885, full:trueโ]
I need to create a Slice based on the USEREMAIL() to verify either he has a right to view particular country data.
This is the table, I need to search.
AND
(
[IsPosted]=TRUE,
IN(USEREMAIL(), SELECT(Tbl_Businessunits[RepresentativeEmailID], [BusinessUnit]=[_THISROW].[xBusinessUnit])))
The above condition does not work as expected.
Any idea please.
Thanks in advance.
John
[/quote]
Hey @John_Henry_Joseph, youโre thinking about things in the right way!
In just about every app I create, one of the first systems I create is exactly what youโre talking about.
Hereโs my suggestion;
This slice then holds that record, and that record alone (provided that thereโs only one record that matches the email in the User table), and from this slice you can easily pull data from the record for whoever is currently logged in and using the app.
Any(Current_User[UserID])
-> pulls the UserID value from the user record
INDEX(Current_User[UserID], 1)
-> does the same thing, but in better waysplit(Current_User[BusinessUnit], " , ")
-> pulls the list of countries (from your table screenshot ) and formats that into a usable list.With the Current_User slice method you can really get some customization going.
Once you have your slice, if you use that SPLIT() formula I showed above, you can easily see if the [xBusinessUnit] is inside the authorized list of countries for your user.
IN([xBusinessUnit], SPLIT(Current_User[BusinessUnit], " , "))
AND(
[IsPosted]=TRUE,
IN(
[_THISROW].[xBusinessUnit],
SPLIT(
LOOKUP(
USEREMAIL(),
"Tbl_Businessunits",
"RepresentativeEmailID",
"BusinessUnit"
)," , "
)
)
)
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |