Is possible tu create a personalize column using a filter as the initial point?

[Maricel]

Hello,

I'm trying to create a productivity chart for the apps used day by day in the company. The BBDD have the list of the apps that we use normally, the problem is that is not possible assign directly in the BBDD adding a column if an app is productive or not, becasue that criteria changes in every department.

So I was thinking, maybe there's a way in the power query (using M or something else) that allows the person using the chart, select is an app is or not productived.

Is possible tu add a filter that contains all the apps used in th company (I meant to show the list of apps) and when someone using the chart marks one or more app in that filter the power query makes and personlize column saying "Productive app" or "Non productive".

[edhans]

I think this can be done. You'd need to take this approach. Create a table that looks like this:

That can be names or departments. I only listed the ones that they have access to.

Bring that in and then create row level security on that table, and relate it to your FACT table on the App column.

Then, drop that entire App column into a Slicer, or in the Filter Pane. When users open the report, it will check their credentials, see what they have access to, then only show those apps in the filter. All other data in the model will be hidden from them if it is related to this App table.

[AlexisOlson]

This doesn't seem to address the part where it's the chart users choosing themselves which apps are useful and which aren't but does look like it should allow for the necessary granularity to select the Apps at the user level.

@Maricel If you need the selections to be controlled by the users, then you may want to look into personal bookmarks, which allow you to save slicer selections.
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-bookmarks

[edhans]

The original question wasn't clear - who determines what users can see what apps. The departments they are in dictate it, or the users decide which ones they consider productive?

I still think RLS is the best way to go, even if the end user does it. If the end user does it, then add the table I put in my original post to a SharePoint List or Excel file, let the users update the lists there then the report shows the accurate info without having to worry about whether or not a bookmark is set properly, or if a report level bookmark has overridden the end user's bookmark.

[AlexisOlson]

Yeah, it wasn't super clear.

I do think the SharePoint suggestion is a good option provided users are familiar with such lists. It's a bit removed from Power BI compared to bookmarks but, as you point out, those aren't a perfect solution either (I don't think there exists a perfect solution to this).

[edhans · Accepted solution]

to make it 100% Power BI, you could write a small Power App that modifies the SharePoint List, Excel File, or whatever your backend is, then embed that into Power BI with the Power App visual. Then end users never have to leave Power BI and it just works via RLS.

But that is a bit more work for the developer of course.