Getting Group/Attributes of a Filtered Item

[ats1958]

I have a dataset of employees, which includes some additional information such as job title, department, and salary.

I want to allow users to select a given employee, then view visualizations for that employee's entire job title and department. For example, user selects Employee A, and then they view a scatter plot showing all the employee salaries in Employee A's department. Ideally, Employee A would be highlighted in this scatter plot

Any way to accomplish this within Power BI?

[v-piga-msft]

Hi @ats1958,

I have found an awesomeblog which may could help you achieve your idea output.

You could have a good look at this blog Highlighting Scatter Charts in Power BI using DAX.

Best Regards,

Cherry

[ats1958]

I don't think this is quite what I need.

In the example, the dots in the scatter plot aren't changing, one is just being highlighted.

What I'm looking to do is display a subset of the data in the chart.

An example would be, if I have a list of cities, states, and populations, and a user selects "San Francisco," I'd want the chart to show all the cities in the California with their populations. And San Francisco would be highlighted in that chart.

[OwenAuger]

@ats1958

Here is a mock-up of what I think you are looking for.

PBIX uploaded here

This could be done a number of ways depending on your existing tables, and my mock-up could well be adapted/simplified.

1. Add an Employee Filter table, containing a single column of Employee names, with an inactive relationship to your fact table.
2. Create a measure Selected Employee Flag, which returns 1 if the currently visible Employees include those selected in the Employee Filter table (intended to be used only when filtering on individual Employees).
   

   Selected Employee Flag = 
   VAR EmployeesSelected =
       CALCULATETABLE (
           VALUES ( Data[Employee] ),
           USERELATIONSHIP ( Data[Employee], 'Employee Filter'[Employee Filter] ),
           ALL ( Data )
       )
   RETURN
       IF (
           NOT CALCULATE (
               ISEMPTY ( VALUES ( Data[Employee] ) ),
               KEEPFILTERS ( EmployeesSelected )
           ),
           1
       )

3. Create another measure Selected Employee Department Flag which returns 1 if the currently visible Departments include those of the Employees selected in the Employee Filter table.
   

   Selected Employee Department Flag = 
   VAR DepartmentSelected =
       CALCULATETABLE (
           VALUES ( Data[Department] ),
           USERELATIONSHIP ( Data[Employee], 'Employee Filter'[Employee Filter] ),
           ALL ( Data )
       )
   RETURN
       IF (
           NOT CALCULATE (
               ISEMPTY ( VALUES ( Data[Employee] ) ),
               KEEPFILTERS ( DepartmentSelected )
           ),
           1
       )

   
   
4. Create your visual (at an Employee level of detail), and add a Visual Level Filter "Selected Employee Department=1"
5. Put the Selected Employee Flag in Color Saturation, and tweak colours as needed.
6. If multiple Employees are selected, they will all be highlighted with relevant Departments shown.

Regards,

Owen

[ats1958]

@OwenAuger This is close. However, I'm using a DirectQuery dataset with Power BI Embedded with RLS. Additionally, end users are uploading their own data to my database. Meaning that adding a new table creates a ton of headaches. Is there a workaround that doesn't involve another table?

Thanks

[OwenAuger]

@ats1958

I can't see any way to avoid creating the additional Employee Filter table, since you need a way of selecting an Employee that doesn't filter your fact table.

Since you are using DirectQuery, you should still be able to add an Employee Filter table relatively easily. The only difference from my uploaded file is that you must create the Employee Filter in Power Query (rather than a DAX Calculated table as I did), for example by referencing your fact table, selecting that one column and using Table.Distinct.

 Someone out there may have some alternatives though?