Dynamic slicer based on USERNAME()

[soldous]

Hi to all PBI masters,

I have a table with all my data and I need to implement a slicer which would filter the data only to my records based on currently logged user. But I need to use ChicletSlicer with only one chiclet called "Mine". Our model is in SSAS Tabular so my first approach was to create a table with all users, add a column with Mine text in all rows, use the Mine column as a slicer, implement RLS on this table and connect the table to my data table. But in this approach, the data is filtered always with the RLS.

I need to filter the data only with the slicer otherwise to show them all.

Could someone help me, please?

Thanks a lot.

[v-lionel-msft]

Hi @soldous ,

Lilke this?

--Before filtering

--After filtering

1. Create a user table and create a relationship.

2. Create a filter measure and add to the table visual.

__Filter = 
IF(
    SELECTEDVALUE('User table'[User]) = USERNAME(),
    1,0
)

If this is not what you want, please describe the data changes before and after filtering.

In addition, whether you want the slicer to switch options dynamically, the current version(2.84.701.0 64-bit (August 2020)) can not do it.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[soldous]

Thank you for the reply @v-lionel-msft 

No, not like this. 

When there is no filter applied I need to show the whole dataset:

And when the filter is applied I need to show only Projects which belong to the curently logged in user:

But I need only one button filter for this and the name of the filter must be Mine. 

Now I have a table of all users with the column Mine where is the string like this for each user: "Mine domain/username". This column is the Category in the Chiclet slicer and the slicer is filtere with this measure:

IF(CONTAINS(Dim_User,Dim_User[User_NK],USERNAME()),1,0)

and it works. But you can see three dots in the slicer because there is a longer string with domain/username. 

What I need is to have only "Mine" text in the column for all users and put this column in the slicer but it doesn't work for me.

[v-lionel-msft · Accepted solution]

Hi @soldous ,

I have the two tables.

1. Create such a table visual.

2. Create such a measure.

__filter = 
IF(
    SELECTEDVALUE('User table'[User]) = USERNAME(),
    1, 0
)

3. Create two buttons.

4. Create two bookmarks

5. Add the 'before filtering' bookmark to the 'Back' button and add 'after filtering' bookmark to the 'Mine' button.

--After I click ‘Mine’

--After I click "Back"

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.