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.
Hey there,
I have a report that I want to have two views:
I have a table with all employee emails and their manager emails, and a table that can toggle between the two modes.
I've tried creating a new column in my employee table "if(selectedvalue(supervisor mode)=True, manger email, email)" with no luck. It only returns the employee email.
The employee table (employee and supervisor data) is used in relationships to several other tables. Ideally, the supervisor mode will filter the employee table and therefore filter the tables it has a relationship to.
Any help is appreciated, been hitting my head against a wall trying to figure it out.
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Mode:
You may create a measurea as below.
Visual Control =
var emp = SELECTEDVALUE('Table'[Employee],"")
var sup = SELECTEDVALUE('Table'[Supervisor],"")
return
IF(
ISFILTERED(Mode[Mode]),
IF(
"employee mode" in DISTINCT(Mode[Mode])&&NOT("supervisor mode" in DISTINCT(Mode[Mode])),
IF(
emp=USERPRINCIPALNAME(),1,0
),
IF(
"supervisor mode" in DISTINCT(Mode[Mode])&&NOT("employee mode" in DISTINCT(Mode[Mode])),
IF(
sup=USERPRINCIPALNAME(),
1,0
),
IF(
"employee mode" in DISTINCT(Mode[Mode])&&"supervisor mode" in DISTINCT(Mode[Mode]),
IF(
OR(
emp=USERPRINCIPALNAME(),
sup=USERPRINCIPALNAME()
),
1,0
)
)
)
)
)
Then you need to put the measure in the visual level filter and use 'Mode' column from 'Mode' table to filter the result.
Finally you may view as 'b@123.com'. Here are the results.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Mode:
You may create a measurea as below.
Visual Control =
var emp = SELECTEDVALUE('Table'[Employee],"")
var sup = SELECTEDVALUE('Table'[Supervisor],"")
return
IF(
ISFILTERED(Mode[Mode]),
IF(
"employee mode" in DISTINCT(Mode[Mode])&&NOT("supervisor mode" in DISTINCT(Mode[Mode])),
IF(
emp=USERPRINCIPALNAME(),1,0
),
IF(
"supervisor mode" in DISTINCT(Mode[Mode])&&NOT("employee mode" in DISTINCT(Mode[Mode])),
IF(
sup=USERPRINCIPALNAME(),
1,0
),
IF(
"employee mode" in DISTINCT(Mode[Mode])&&"supervisor mode" in DISTINCT(Mode[Mode]),
IF(
OR(
emp=USERPRINCIPALNAME(),
sup=USERPRINCIPALNAME()
),
1,0
)
)
)
)
)
Then you need to put the measure in the visual level filter and use 'Mode' column from 'Mode' table to filter the result.
Finally you may view as 'b@123.com'. Here are the results.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Allan! @v-alq-msft
Thanks for the help, I adapted your code to my PBIX. IS there anyway to adapt the solution to have the mode directly filter the Employee Table inplace of just a visual filter?
I neglected to mention that my Employee table is related to several other tables that it filters.
Hi, @Anonymous
I don't think you can adapt the solution to have the model directly filter the Employee Table. You may try using query parameter to filter the model in Power Query. However, you are unable to get the current user e-mail to filter the specific user.
About query parameters in Power BI, you may refer to the following links.
Deep Dive into Query Parameters and Power BI Templates
Power BI Desktop Query Parameters
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You could use the following
Create 3 tables:
table 1: "User list" (unique email id)
table 2 : "employee view"
table 3 : "supervisor view"
Table 2 and 3 are coipies of each other.
Then you can create relationships
1. user list with employee email in table 2
2. user list with supervisiou email in table 3
Then apply RLS on table 1 user.
This way you can create 2 views. The views using columns from table 2 will be the "employee view" and the views using columns from the table 3 will be the "supervisor view"
PS: Please mark this post as a "solution" if this helps solving the issue and/or hit the "thumbs-up" button if you found this insighful. Thanks.
Hey @Helper !
If I understand your solution correclty I would have to apply the duplication process to all of the tables that my employee table is related to. Yah? (I neglected to mention these relationships in the original description)
Appreciate the feedback !
Hi @Anonymous - data seems incomplete. What i understood is you have 2 different tables? Employee and Supervisor, how are they related/Linked to each other?
Can you give some sample data and their relation please? like the one below
Thanks,
-Namish B
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |