locked
Disconnected Table Slicer - Values are not being 'visually indicated' correctly RRS feed

  • Question

  • I have a disconnected table that I am using to display projects that a person is either project manager or project director for.

    The slicer itself is working fine and it updates my pivot correctly - the problem is with the 'visually indicate' which items have data functionality.

    A bit more information for you:

    Because we have such a large list of names (around 5,000) I have included a First Name, Last Name filter option.
    The first name and last name filters are working - I can see the results diminish if I choose First Name = Z.
    The Employee filter is working - I can see the projects of a certain person (or people) if I choose them.

    This is the measure on my pivot chart:

    IF(
       CONTAINS(
           VALUES('Project Role'[EmployeeKey]),
           'Project Role'[EmployeeKey],
           MAX(Project[ProjectDirectorKey])
       )  ||
       CONTAINS(
    VALUES('Project Role'[EmployeeKey]),
           'Project Role'[EmployeeKey],
           MAX(Project[ProjectManagerKey])
      ),
       [Sum of Total Costs],
       BLANK()
    )

    With all the slicers cleared the 'Employee' slicer shows in darker blue two people, the person with the highest projectdirectorkey value and the person with highest projectmanagerkey value.

    This leads me to believe that the Employee slicer is somehow responding to the MAX(Project[ProjectDirectorKey]) and MAX(Project[ProjectManagerKey]) values - which isn't what I want it to do. I  would like it to be responding to the rows of data available in the pivot chart.

    Can anyone shed some light on this and provide a solution?

    Thanks
    Paul

    Thursday, June 12, 2014 8:45 AM

Answers

  • ok, it seems that you want to filter your data by a given employee. The employee can either be the Project Director or a Project Manager. You want to see all facts associated to an employee regardless of its role (Director or Manager)

    you further have an 'Project Role'-table that holds Employee data with columns for FirstName, LastName etc. and calculated columns as LEFT(FirstNAme, 1) and same for LastName which is used in the slicer

    there is no active relationship between 'Project Role' and 'Project' and for your measure you are creating a virtual relationship between 'Project Role' and Project to mimic the OR behaviour

    if the above is correct try this measure:

    MyMeasures:=CALCULATE(
    [Sum of Total Costs],
    FILTER('Project',
        CONTAINS(
            VALUES('Project Role'[EmployeeKey]), 
           'Project Role'[EmployeeKey], 
           Project[ProjectDirectorKey])
        ||
        CONTAINS(
            VALUES('Project Role'[EmployeeKey]), 
           'Project Role'[EmployeeKey], 
           Project[ProjectManagerKey]))
    )

    can you try this?

    hth,
    gerhard

        


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Paul Trower Thursday, June 12, 2014 10:59 AM
    Thursday, June 12, 2014 10:53 AM
    Answerer

All replies

  • ok, it seems that you want to filter your data by a given employee. The employee can either be the Project Director or a Project Manager. You want to see all facts associated to an employee regardless of its role (Director or Manager)

    you further have an 'Project Role'-table that holds Employee data with columns for FirstName, LastName etc. and calculated columns as LEFT(FirstNAme, 1) and same for LastName which is used in the slicer

    there is no active relationship between 'Project Role' and 'Project' and for your measure you are creating a virtual relationship between 'Project Role' and Project to mimic the OR behaviour

    if the above is correct try this measure:

    MyMeasures:=CALCULATE(
    [Sum of Total Costs],
    FILTER('Project',
        CONTAINS(
            VALUES('Project Role'[EmployeeKey]), 
           'Project Role'[EmployeeKey], 
           Project[ProjectDirectorKey])
        ||
        CONTAINS(
            VALUES('Project Role'[EmployeeKey]), 
           'Project Role'[EmployeeKey], 
           Project[ProjectManagerKey]))
    )

    can you try this?

    hth,
    gerhard

        


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Paul Trower Thursday, June 12, 2014 10:59 AM
    Thursday, June 12, 2014 10:53 AM
    Answerer
  • Awesome - thank you so much.

    Completely understood my requirements, worked perfectly first time.

    Paul

    Thursday, June 12, 2014 10:59 AM