How to Create Dynamic Default Slicer Value?

[SensingFailure]

I have been following the steps from this post on creating a default slicer selection based on a user's location.

Everything appears to be working correctly except I am unable to place the Visual Filter Pane onto the "Filters on this visual" and select when the value is 1.  It appears on the filter panel, but remains greyed out.

The only time I am able to make it work is when I go to a new page and create a simple table.  Any thoughts on what I am missing?

[v-zhouwen-msft]

Hi @SensingFailure ,

Regarding your issue, it seems that you only have one quick measure in your visual, which should be the problem.Make sure there is at least one field in the visual other than a quick measure

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

[SensingFailure]

@v-zhouwen-msft Thanks for responding!  Unfortunately, I don't have the option to not use a quick measure as 'Don't Summarize' is not visible.  I have ensured the field is set to a text type, but there are a lot of complex relationships throughout the dashboard.  Additionally, the table does not contain duplicate values for PostName.  All other datasources are relationally matched to the 'ORION - Posts' table via PostName.  Could this be the issue?

 

[v-zhouwen-msft]

Hi @SensingFailure ,

Regarding your question, please use a table visual. Did you set the x-axis of this visual?The Y axis of this visual you are using now will only show the aggregated values. So there is no 'Don't Summarize' option.If you don't add a field in x-axis.There is only one value after aggregation, so you cannot set 'Visual Filter Pane = 1'.

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

[SensingFailure]

Thanks @v-zhouwen-msft , but using a table visual is not what I am looking for.  I have a very complex page conveying a bunch of information that is all tied to the ORION-Posts table.  Essentially, I just wanted to make an option called "Current Country" that would use the value from the user's information (if it exists) as the value from ORION-Posts.

Unfortunately, I have not found a way to use the values from ORION-Posts when the slicer must use a new table created from ORION-Posts (Slicer Table), but with the additional option of "Current Country".  Ideally it would look like this:

1. Slicer Table is populating the Slicer which is set to "Current Country"
2. The "Current Country" value is pulled from the Country column that matches the logged in user's PrincipalUserName()
3. The Country value from the User table is then matched to ORION-Posts and this value is used through the rest of the dashboard to filter all visuals.

What are your thoughts here?  

[SensingFailure · Accepted solution]

I actually was able to crack this problem!  I will attempt to break down how I achieved this.

I created a Slicer Table with:

Slicer Table = 
UNION(
    SELECTCOLUMNS(
        'ORION - Posts',
        "PostName", 'ORION - Posts'[PostName]
    ),
    DATATABLE(
        "PostName", STRING,
        {{"Current Post"}}
    )
)

Then I have a user table with the locations tied to their principal user names by email and use this measure to lookup the post name:

UserPostName = 
LOOKUPVALUE(
    'UserLocationData'[PostName],
    'UserLocationData'[email], USERPRINCIPALNAME()
)

Back in the Slicer Table, I created another measure to use the user's post name if "Current Post" is selected in the slicer.

SelectedPostName = 
IF(
    SELECTEDVALUE('Slicer Table'[PostName]) = "Current Post",
    [UserPostName],
    SELECTEDVALUE('Slicer Table'[PostName])
)

The biggest issue I was having was that other measures would not calculate correctly.  This is solved by adding a variable into each measure.

LES Count = 
VAR SelectedPost = 
    IF(
        SELECTEDVALUE('Slicer Table'[PostName]) = "Current Post",
        [UserPostName],
        SELECTEDVALUE('Slicer Table'[PostName])
    )

VAR EmpCount = 
    CALCULATE(
        SUM('DAFI - Personnel Data'[count]),
        'DAFI - Personnel Data'[employeeType] = "Locally Employed Staff (LE Staff)",
        'DAFI - Personnel Data'[site] = SelectedPost
    )
    
RETURN
IF(ISBLANK(EmpCount), 0, EmpCount)

However, I had to deactivate the relationships for:

• 'Slicer Table'[PostName] to 'ORION - Posts'[PostName]
• 'UserLocationData'[PostName] to 'ORION - Posts'[PostName]