Forum Discussion

SensingFailure's avatar
SensingFailure
Frequent Visitor
2 years ago
Solved

How to Create Dynamic Default Slicer Value?

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...
Need Help
  • SensingFailure's avatar
    SensingFailure
    2 years ago

    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]
SensingFailure's avatar
SensingFailure
Frequent Visitor
2 years ago

Thanks Anonymous , 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's avatar
SensingFailure
Frequent Visitor
2 years ago

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]