Forum Discussion

David1205's avatar
David1205
Frequent Visitor
2 years ago
Solved

DATA Slice Interaction

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • David1205's avatar
    David1205
    Frequent Visitor
    2 years ago

    Example:

     

    Data Structure:

    NameDepartment
    LuisMarketing
    FrankMarketing
    PeterM&A
    ValentinMarketing

     

    In my report I set the data slicer "name" to "Valentin". The following table should then be displayed in my report: 

    NameDepartment
    LuisMarketing
    FrankMarketing
    ValentinMarketing
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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