Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community,
I have a table with employee names and the department they work in. I have a data slice in Power BI Desktop that allows me to filter by the desired employee. I now want to add a table in the report that lists all employees who work in the same department as the filtered employee. The problem is that I have to set the interaction of the data slice with the employee name with the desired table to "none", otherwise only the employee filtered in the data slice will be displayed in the table, but not all employees in the department. How can I solve this problem?
Thanks for your help!
Solved! Go to Solution.
Hi @David1205 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create a name dimension table (DO NOT create any relationship with the fact table)
Employees = VALUES('Table'[Name])
2. Create a measure as below
Flag =
VAR _emps =
ALLSELECTED ( 'Employees'[Name] )
VAR _depts =
CALCULATETABLE (
VALUES ( 'Table'[Department] ),
FILTER (ALLSELECTED( 'Table'), 'Table'[Name] IN _emps )
)
RETURN
IF ( SELECTEDVALUE ( 'Table'[Department] ) IN _depts, 1, 0 )
3. Create a slicer which apply the field [name] of dimension table and a table visual. Then apply a visual-level filter for the table visual with the condition (Flag is 1)
Best Regards
Example:
Data Structure:
Name | Department |
Luis | Marketing |
Frank | Marketing |
Peter | M&A |
Valentin | Marketing |
In my report I set the data slicer "name" to "Valentin". The following table should then be displayed in my report:
Name | Department |
Luis | Marketing |
Frank | Marketing |
Valentin | Marketing |
Hi @David1205 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create a name dimension table (DO NOT create any relationship with the fact table)
Employees = VALUES('Table'[Name])
2. Create a measure as below
Flag =
VAR _emps =
ALLSELECTED ( 'Employees'[Name] )
VAR _depts =
CALCULATETABLE (
VALUES ( 'Table'[Department] ),
FILTER (ALLSELECTED( 'Table'), 'Table'[Name] IN _emps )
)
RETURN
IF ( SELECTEDVALUE ( 'Table'[Department] ) IN _depts, 1, 0 )
3. Create a slicer which apply the field [name] of dimension table and a table visual. Then apply a visual-level filter for the table visual with the condition (Flag is 1)
Best Regards
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |