Another Very clever solution, by Bas!!! In my opinion, it is important to note that this solution does not offer data protection per se, i.e. it does not guarantee that the user will only be able to see the data that concerns them. For this, you will have to continue to implement the RLS.
Hi Bas, Just two days ago, I was asked if, rather than making separate reports for each user, I could automatically filter reports based on who was viewing them. I thought the request was absurd :) now i know its not ...thanks so much for sharing!
@@HowtoPowerBI - I was actually planning on doing something similar with RLS. I have a report requirement where stakeholders can see all data, but a staff member can only see their own numbers. But there is also a need for stakeholders to see one metric that should NOT be visible to staff. Since I have Active Director groups already in place with RLS, I plan to add a filter requirement to the staff group that removes the ability to see one of the metrics. Similar to how you set this up with the Parameter. This is a fantastic solution. Thank you for sharing.
Big time fan of your work Bas! Quick question - I've a similar use case as addressed in this video. The only difference is - One KPI can be common between 2 user groups AND one user could need to look at both "sets" of KPIs. How do we achieve that?
This was really helpful for me , however I think reference labels on new card visual is not working when we use the parametric table , Do you have a solution to this , can you try adding reference labels on these
I have already did it by myself a month ago. Nice!i don’t remeber but it did not use this filter panel for sure. Just other table with other parameter like email adress
Great video! I only wish you'd paused to explain how that TopN filter works, because most people won't understand how a filter that only shows the Top 1 value from a column could possibly show more than 1 value.
I might be wrong but will give it a crack. I assume that the measure gets evaluated for each filter context in the chart. The measure will evaluate to either a 1 or a 0. Since Bas has specified top 1, all values equal to the largest (in this case 1) will be kept by the filter.
@@PeterKontogeorgis Yes, that's the same conclusion I reached as well. I just wish Bas had explained it in the video, because it's not exactly intuitive, and I think that a lot of people won't understand what's actually happening. When learning a new skill, understanding the underlying concepts is even more important than being able to follow instructions and replicate an example step-by-step. The real world will never be the same as the example, so if you don't actually understand the underlying concepts, you won't be able to apply them to your specific real world use cases. I think this is probably an example of a very common problem, where expert instructors sometimes forget to explain certain things that seem totally obvious to them, but which might not be obvious to a beginner.
Question: If we want to specify that view as you did, what about creating a view for an "admin", so he would see all of the stuff - not only categorized to him by an email.
Actually this matched my requirement but i am having one challenge, for each kpi we have a separate trend page which we are implementing but placing a top of the kpi visual and giving navigation to other page.can you please guide how to achieve this scenario dynamically
Is there a way to convert timestamps into Shift Codes for work? So if a form gets submitted on 1st shift, it'll just convert the timestamp to show as first shift?
Here you are saying about the Employee table, having the group-id-kpi, but we need a dedicated emplyee table for every email id with corresponding group-id-kpi, which is a drawback know....or does that mean employee table contains the role of each of the email id, like whether he is a manager, admin, sales rep like that ? pleaase answer
thank you! nope, rls is a security feature to put a filter on the underlying dataset depening on who is looking at the report. This is not a security feature, just a ux improvement
Is there anyway you can make the kpi cards disappear only when an employee has value 0 in one of the categories? Instead of making groups for them. In my case every employee can be part of different KPI groups.
yes. you could adjust the measure so that it defaults to a default kpi group in case the user is not in the employee table or no kpi group has been assigned
Interesting approach but it has a small inconvenience :) Every time when a new person appears, who must belong to one of the groups, the table must be rewritten :) An option would be with an external table that respects the initial structure and that you can be modify and loads the structure at the time of refresh
It can be achieved by creating an Excel file and placing it in a SharePoint site, can be edited whenever required and changes will reflect on the next refresh making sure only admin has access to file for editing in case of security concerns.
@@___rnd That is indeed true, but from my experience, keeping things simple, without any excel mapping tables is the best approach. Just plain load via PQ, no transformation. Of course that means you have to have very good backend source. If there is any mapping table, it should come from system, otherwise the maintenance of the dataset will be pain. And imagine that if you should maintain more than 15 datasets, each connected somehow to Excel. Been there, done that, please never again. I take this video more as a gimmick and technical challenge (Top N filtering,..). I mean, this channel could be renamed to "How to break Power BI" since Bas always do similar (and interesting nonetheless) challenges.
not really :p .. rls is a security feature to put a filter on the underlying dataset so that a person can only see that part of the data set. This idea just improves ux -> show the relevant kpis to the user that is looking at the report.
The way you explain things and build up the whole video from simple to advanced, step by step, is just so good. You are great at teaching.
Another Very clever solution, by Bas!!!
In my opinion, it is important to note that this solution does not offer data protection per se, i.e. it does not guarantee that the user will only be able to see the data that concerns them. For this, you will have to continue to implement the RLS.
If the user has Viewer role, i think the solution is correct. How else could see the other data?
Thanks, we are working on a report which needs this... amazing approach. I am going to implement this but it's just for slicer selection.
Hi Bas, Just two days ago, I was asked if, rather than making separate reports for each user, I could automatically filter reports based on who was viewing them. I thought the request was absurd :) now i know its not ...thanks so much for sharing!
if you would like to put a filter on the report based on who is viewing then you probably better off using rls
@@HowtoPowerBI - I was actually planning on doing something similar with RLS. I have a report requirement where stakeholders can see all data, but a staff member can only see their own numbers. But there is also a need for stakeholders to see one metric that should NOT be visible to staff. Since I have Active Director groups already in place with RLS, I plan to add a filter requirement to the staff group that removes the ability to see one of the metrics. Similar to how you set this up with the Parameter.
This is a fantastic solution. Thank you for sharing.
This is a really cool idea. It would never have occurred to me to do the Top N filtering as you did. Nice technique!
thank you! the topn filtering trick you can use for many nice things :D
Great to see you back Master! 🎉
😊
Just brilliant. Love your videos man. Great stuff.
thanks for watching them !
Amazing content, super impressed with the way you simplify things, thank you very much 👍👍
Big time fan of your work Bas!
Quick question - I've a similar use case as addressed in this video. The only difference is - One KPI can be common between 2 user groups AND one user could need to look at both "sets" of KPIs. How do we achieve that?
Thanks Bas this was awesome
Bas. You are truly a geniuss❤
As always something new to learn. Thank you.
happy to hear that! thanks for watching! :D
Brilliant, thought you where going to be using row level security but this is much better
thank you
as usual Class, thank you for sharing Bas
Genius as usually Bas!
thank you! :D
The person detection trick in here is awesome on its own
it opens up many personalization options (beyond row level security :) )
You are the best Bass
I love your videos ! It would be good if you make one about navigation experience for the user with all type o navigations 🙏🏽
This was really helpful for me , however I think reference labels on new card visual is not working when we use the parametric table ,
Do you have a solution to this , can you try adding reference labels on these
I have already did it by myself a month ago. Nice!i don’t remeber but it did not use this filter panel for sure. Just other table with other parameter like email adress
Amazing Work mate
Thank you! Cheers!
This is absolutely fantastic!
😎😊
Good job! It's a great idea!! 🎉 I love It! ❤
thank you!!! 😊
Sir, which videos you would suggest for the beginners like me. Just now learnt excel and doing data analysis course
th-cam.com/video/w4NHK_jizUw/w-d-xo.html
Brilliant as usual
Great video! I only wish you'd paused to explain how that TopN filter works, because most people won't understand how a filter that only shows the Top 1 value from a column could possibly show more than 1 value.
I might be wrong but will give it a crack.
I assume that the measure gets evaluated for each filter context in the chart. The measure will evaluate to either a 1 or a 0. Since Bas has specified top 1, all values equal to the largest (in this case 1) will be kept by the filter.
It’s a very clever way to apply this filter btw!
@@PeterKontogeorgis Yes, that's the same conclusion I reached as well. I just wish Bas had explained it in the video, because it's not exactly intuitive, and I think that a lot of people won't understand what's actually happening. When learning a new skill, understanding the underlying concepts is even more important than being able to follow instructions and replicate an example step-by-step. The real world will never be the same as the example, so if you don't actually understand the underlying concepts, you won't be able to apply them to your specific real world use cases. I think this is probably an example of a very common problem, where expert instructors sometimes forget to explain certain things that seem totally obvious to them, but which might not be obvious to a beginner.
Question:
If we want to specify that view as you did, what about creating a view for an "admin", so he would see all of the stuff - not only categorized to him by an email.
Game changer!
Does this work if the reports are embedded in service portals?
Actually this matched my requirement but i am having one challenge, for each kpi we have a separate trend page which we are implementing but placing a top of the kpi visual and giving navigation to other page.can you please guide how to achieve this scenario dynamically
Is there a way to convert timestamps into Shift Codes for work? So if a form gets submitted on 1st shift, it'll just convert the timestamp to show as first shift?
Waw thank u 🎉
Here you are saying about the Employee table, having the group-id-kpi, but we need a dedicated emplyee table for every email id with corresponding group-id-kpi, which is a drawback know....or does that mean employee table contains the role of each of the email id, like whether he is a manager, admin, sales rep like that ? pleaase answer
Just 1 table.. you can check the file (download link in description)
Incredible! Is it not the same than RLS?
thank you! nope, rls is a security feature to put a filter on the underlying dataset depening on who is looking at the report. This is not a security feature, just a ux improvement
Is there anyway you can make the kpi cards disappear only when an employee has value 0 in one of the categories? Instead of making groups for them. In my case every employee can be part of different KPI groups.
Plz make video on how to use Charculator chart and other custom chart
Why not use RLS in this case?
How to get the data which you use in your video for practice purpose
Link in description
Can you suggest a view when the KPI group is not set for a user and they try to access this report?
yes. you could adjust the measure so that it defaults to a default kpi group in case the user is not in the employee table or no kpi group has been assigned
Interesting approach but it has a small inconvenience :)
Every time when a new person appears, who must belong to one of the groups, the table must be rewritten :) An option would be with an external table that respects the initial structure and that you can be modify and loads the structure at the time of refresh
It can be achieved by creating an Excel file and placing it in a SharePoint site, can be edited whenever required and changes will reflect on the next refresh making sure only admin has access to file for editing in case of security concerns.
@@___rnd That is indeed true, but from my experience, keeping things simple, without any excel mapping tables is the best approach. Just plain load via PQ, no transformation. Of course that means you have to have very good backend source. If there is any mapping table, it should come from system, otherwise the maintenance of the dataset will be pain. And imagine that if you should maintain more than 15 datasets, each connected somehow to Excel. Been there, done that, please never again.
I take this video more as a gimmick and technical challenge (Top N filtering,..).
I mean, this channel could be renamed to "How to break Power BI" since Bas always do similar (and interesting nonetheless) challenges.
What if a KPI belong to multiple groups?
Genius!
thank you!!!
Hi Bas
one user to see two group id's
how to achieve that
hmmm , something to think about ;)
Bas is always Boss
😀 haha
Rather than writing a KPI dax measure you could have also created a relationship directly from KPI group ID to KPI parameter Table.
Takk!
thank you so much!!! 😊
RLS without the RLS constraints!!
not really :p .. rls is a security feature to put a filter on the underlying dataset so that a person can only see that part of the data set. This idea just improves ux -> show the relevant kpis to the user that is looking at the report.
supercalifragilisticexpialidocious
♫♫♫♫