Forum Discussion

soldous's avatar
soldous
Advocate II
6 years ago
Solved

Dynamic slicer based on USERNAME()

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 ChicletSli...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

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's avatar
soldous
Advocate II
6 years ago

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's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.