Search code examples
powerbidaxpowerbi-desktopmeasure

Cross Teams Visualizations

Hi StackOverflow Masters,

I would like to aks about your help to resolve my PowerBI issue.

My task is to create visualization that shows projects where is multiple teams and shows some differentiation for those teams. I have few tables: Projects, Employees, EmployeeBookings,Teams, Calendar. I've created a meassure that I use in 100% stacked bar chart. I need to be able to filter by team name (from Team table), employee name(from Employee table) and fiscal year(from Calendar table). My solution works fine for filter by team and fiscal year but not show expected result when I filter by resource name. If I choose John John, for example, my visualization should show projects where his team has worked or still working with the other teams they collaborate on those projects. Right now visualization shows projects he has worked on considering only his team. I would add that filtering must work for multiple selections from different slicers

Visualization when I'm filtering by Teams:

Filtering by Team

Result when I'm filtering by resource name:

Filtering by resource name

My messure used in visualization.

Count Cross Team 6 = 
VAR SelectedTeams = VALUES ( Teams[Name] )
VAR SelectedEmployees = VALUES ( Employees[Name] )
VAR SelectedTeamsAndEmployees = UNION ( SelectedTeams, SelectedEmployees )

VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            ALLSELECTED ( EmployeeBookings ),
            "Projects", RELATED ( Projects[Name] ),
            "Team", RELATED ( Employees[Team] ),
            "Employee", RELATED ( Employees[Name] )
        ),
        [ID Project],
        [Projects],
        [Team],
        [Employee]
    )

VAR b =
    COUNTROWS ( FILTER ( a, [Projects] IN VALUES ( Projects[Name] ) ) )

RETURN
    IF (
        ISFILTERED ( Teams[Name] ) && COUNTROWS ( SelectedTeamsAndEmployees ) > 0,
        IF (
            b > 1,
            COUNTROWS (
                FILTER (
                    a,
                    [Projects]
                        IN VALUES ( Projects[Name] )
                            && [Team] IN SelectedTeamsAndEmployees
                )
            )
        ),
        IF (
            ISFILTERED ( Employees[Name] ) && COUNTROWS ( SelectedTeamsAndEmployees ) > 0,
            COUNTROWS (
                FILTER (
                    a,
                    [Projects]
                        IN VALUES ( Projects[Name] )
                            && [Employee] IN SelectedTeamsAndEmployees
                )
            ),
            IF (
                b > 1,
                COUNTROWS (
                    FILTER (
                        a,
                        [Projects]
                            IN VALUES ( Projects[Name] )
                                && [Team] IN VALUES ( Employees[Team] )
                    )
                )
            )
        )
    )

This is my fictional model that reflects the situation in my real project. My dummy model

Link if anyone would like to download pbix file. pbix file

I've tried to change my measure to include a section to filter by resource, but I don't know if I'm able to somehow incorporate the filtering option into my measure. I tried to create an additional table where I collect columns from the Teams, Project, EmployeeBookings and Employee tables, connect by a relationship to the Projects table and test if the filtering works if I use the columns from the newly created table in the slicer.

If anyone could help solve this issue, I would be very grateful. Thanks in advance!

Solution

  • You were almost there. The issue is your Employee slicer was coming from the Employees table where the Team column was used in the chart legend. So as you slice on Employee it will also slice on Team - meaning you will never see outside/cross teams.

    Steps to get you going.

    1. Create a new disconnected table just for the Employee slicer

    Slicer Employee = SUMMARIZECOLUMNS(Employees[ID Employee], Employees[Name])

    Then update your slicer to use this new table (Slicer Employee[Name])

    2. Update your Measure to refer to the Slicer Employee table

    Count Cross Team 6 = 
  VAR SelectedTeams = VALUES ( Teams[Name] )
  VAR SelectedEmployees = VALUES ( 'Slicer Employee'[Name] )
  VAR SelectedTeamsAndEmployees = UNION ( SelectedTeams, SelectedEmployees )

  VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            ALLSELECTED ( EmployeeBookings ),
            "Projects", RELATED ( Projects[Name] ),
            "Team", RELATED ( Employees[Team] ),
            "Employee", RELATED ( Employees[Name] )
        ),
        [ID Project],
        [Projects],
        [Team],
        [Employee]
    )

  VAR b =
    COUNTROWS ( FILTER ( a, [Projects] IN VALUES ( Projects[Name] ) ) )

RETURN
  SWITCH( TRUE(),
    ISFILTERED( Teams[Name] ) && COUNTROWS ( SelectedTeamsAndEmployees ) > 0 && b > 1,
        COUNTROWS (
            FILTER (
                a,
                [Projects]
                    IN VALUES ( Projects[Name] )
                        && [Team] IN SelectedTeamsAndEmployees
            )
        ),
    
    ISFILTERED ( 'Slicer Employee'[Name] ) && COUNTROWS ( SelectedTeamsAndEmployees ) > 0,
        COUNTROWS (
            FILTER (
                a,
                [Projects]
                    IN VALUES ( Projects[Name] )
                        && [Employee] IN SelectedTeamsAndEmployees
            )
        ),
    
    b > 1,
        COUNTROWS (
            FILTER (
                a,
                [Projects]
                    IN VALUES ( Projects[Name] )
                        && [Team] IN VALUES ( Employees[Team] )
            )
        )
  )

    enter image description here