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 PBIX file with data import model. The report page XXX needs to defualt to a value in the User table based on USERNAME(). However, the user must be able to view all data for other users as well, by manually clearing the default filter on the User table.
This feature has been brought up a few times, but I want to hear from people who have managed to solve this in a pretty way 🙂
Thanks.
Solved! Go to Solution.
By Tabular model I just meant an import data source. I managed to solve this in a satisfactory way though. I made a Yes/No parameter table and added a dropdown slicer filtered to "Yes" to the report, I then Created two measures, one with no filter, and one filtered to USERNAME(), then created this measure to dynamically switch between the two measures, depending on the value in the parameter-table:
IF ( HASONEVALUE ( IsCurrentUser[CurrentUser] ); // My Parameter Table SWITCH ( VALUES ( IsCurrentUser[CurrentUser] ); // My Non-filtered SalesQty "Yes"; SalesFact[SalesQty(CurrentUser)]; // My USERNAME() filtered SalesQty "No"; SalesFact[SalesQty(AllUsers)] ); SalesFact[SalesQty(AllUsers)] )
Now the report will default to the current user, but he can clear the filter on the parameter dropdown slicer to see all users.
It was actually alot more simple than first expected, as I had a little time to get around it 🙂
Thanks for input though.
RSK
Hi @Anonymous,
If my understanding is correct, you import Tabular model to Power BI, and you set USERNAME() in the model. "The report page XXX needs to defualt to a value in the User table based on USERNAME()." means userA can only see userA's value and userB can only see userB's value in report page. All the settings were done in the tabular model, so we can not let the userA see all data. Because we can not set it in Power BI.
Best Regards,
Angelia
By Tabular model I just meant an import data source. I managed to solve this in a satisfactory way though. I made a Yes/No parameter table and added a dropdown slicer filtered to "Yes" to the report, I then Created two measures, one with no filter, and one filtered to USERNAME(), then created this measure to dynamically switch between the two measures, depending on the value in the parameter-table:
IF ( HASONEVALUE ( IsCurrentUser[CurrentUser] ); // My Parameter Table SWITCH ( VALUES ( IsCurrentUser[CurrentUser] ); // My Non-filtered SalesQty "Yes"; SalesFact[SalesQty(CurrentUser)]; // My USERNAME() filtered SalesQty "No"; SalesFact[SalesQty(AllUsers)] ); SalesFact[SalesQty(AllUsers)] )
Now the report will default to the current user, but he can clear the filter on the parameter dropdown slicer to see all users.
It was actually alot more simple than first expected, as I had a little time to get around it 🙂
Thanks for input though.
RSK
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |