Protecting data with a password inserted into a text filter - high level, feedback appreciated!

[therealseabass]

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:

1. Row-level security
   1. 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
2. Using a free text filter/slicer visual - This doesn't work in a simple way for the following combination of reasons
   1. 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)
   2. The search uses "contains" to match any substring to the closest string in a column of a table
   3. 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
3. Best visuals to use for this problem
   1. 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)
   2. 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)
   3. 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:

1. 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
2. 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-... 
   
   • 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

[therealseabass]

Found two solutions!

1. Smart Filter Pro isn't actually as pricey as I thought, I just misinterpreted and clarified over email with some folks! This would perfectly solve our problem
2. Row-level security works for us!
   
   • The issue we'd had here was just that we didn't want user emails to be associated with sensitive data, and we had to dynamically create these rules which seemed to not be possible: solution below
     
     1. Separate sensitive data into two different Sharepoint lists that are refreshed into the PowerBI dataset: by doing this, one of the Sharepoint lists has sensitive data associated with arbitrary IDs, then the other has user emails associated with those same arbitrary IDs - this allowed us to refresh two separate PowerBI tables, giving more flexibility to hide sensitive data to users associated with a specific email. (Again, the reason we used Sharepoint lists + dataset refreshing is because this can easily be automated using PowerAutomate)
     2. Now, the Sharepoint list containing email + data_access_ID can be manipulated by the PowerAutomate flow as new users come into this pipeline - then, we link this email table to the actual data table and add row-level security which pulls user email to see what the user can view! There is much clearer detail on this in the below article, basically follow the instructions there.
        • Power BI Table Based Row Level Security (mssqltips.com)