Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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:
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.
@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?
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.
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:
What are your thoughts here?
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
59 | |
59 |