Protecting data with a password inserted into a text filter - high level, feedback appreciated!
Hello! For the past few weeks, I've been working on doing what the title says, revealing only sections of data to users who input an ID, revealing all related data in our visuals. Here is an example of what this table might look like: ID MetricToView ReadableName_1342135 23 OtherName_1341147 27 Here are the approaches I've explored: Row-level security Might be the path forward, but our thought was this won't work because we want new users to dynamically get access to rows of data - Power Automate doesn't provide that functionality and it looks like this would require a lot of upkeep to continually manually assign users Using a free text filter/slicer visual - This doesn't work in a simple way for the following combination of reasons All possible options for a field are listed and can't be hidden (since this is somewhat sensitive data, we don't want users to be able to pick an ID that is not theirs to see data that isn't theirs) The search uses "contains" to match any substring to the closest string in a column of a table The search doesn't allow for us to set a "single selection mode" - This in combination of the above point removes one possible solution I attempted Best visuals to use for this problem Using Smart Filter Pro - This will work out of the box for a mid-high yearly fee (has options to hide suggestions and enforce exact match) Using Text Filter - This is great because it is free and doesn't list suggestions, but has the problems listed above (1.2 and 1.3, using 'contains' to search and not allowing single selection mode) Other options - I looked at a ton of other visuals (I believe every other option), and all options had dropdowns for a selection which we don't want Ways I've approached this problem to try to solve it using the free Text Filter visual: Most realistic, least work, ugly - Remove the user readable portion of the passcode IDs The readable portion is useful for someone to be able to recognize their ID, but with the default filter behavior, if I suspect that "John" has some data, I can search "John" and I might be able to view that If this "solution" is followed, now there would just be a string of digits as IDs, so even if someone enters '132' and that matches to a single ID, I won't know who it belongs to or even if that is a single match or multiple matches I still don't love this solution because we don't want users to compare their metric to another user, so even getting an idea of other values that have been given isn't favorable Convoluted but cool, two routes to a solution that I've explored and found not possible - Creating 2 duplicates of each password ID with the first or last character replaced by an underscore - https://community.fabric.microsoft.com/t5/Power-Query/write-unique-password-to-see-the-data-of-that-partucal-person/m-p/1152259 This would work if single selection was possible, as some substring input into the text box would now latch on to the false password, and no data would be shown As of now, multiple selection is the only option with this visual, so multiple matching rows are returned, and if any row contains valid data, the visuals are populated Then, I tried creating a measure that would count the number of selected rows - this correctly found all rows that were filtered on by the text search visual, but there is no secure way to stop the visuals from showing in this case I know we can change the transparency/color of visuals, but aren't they still screen-readable or accessible in different color schemes of a browser? I know this is a lot of context, but my question is: Is there a way to password protect some data using this text filter with the knowledge that it doesn't perform an exact match, and that there is no option to set a single match? - Thank you! One path that I think might yield a solution is if we can have the text filter search one table (filled with false, distractor IDs), and if no match is found, search the real password table - I just don't know if this is possible One other path could be some other manipulation of visuals (or filtering if possible) based on the value of a measure which checks if there is truly one match or not Anything else - If row-level security can be automated somehow, please let me know too! - otherwise, the other solutions I have listed might be what I pursue