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]
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

Anonymous 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?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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'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]