none
Getting a slicer that contains a single list of PMs and PDs

    Question

  • We have a project table, each project has a project manager and a project director.

    What I would like is a slicer that has all the project managers and project directors listed on it (some people are both project managers and project directors). When a name is clicked on the list I would like the pivot table to be filtered such that it shows the projects that the person is either project manager or project director of.

    Can someone point me in the right direction?

    Thanks

    Paul

    Wednesday, May 21, 2014 1:32 PM

Answers

  • I would probably try to use Disconnected Tables/Slicers to accomplish something like this.  There is a lot of info on them out there if you search for it.

    Basically, it involves adding a table to your data model with no relationships to other tables in your model.  This gives you a way to input parameters using slicers.

    As a simple example, lets say your Project table contains the following fields:

    ProjectID

    DirectorID

    ManagerID

    ProjectCost

    Now you would add a new disconnected table to your model called something like DimEmployee which is a unique list of all Managers and Directors.  Don't relate it to Projects. It would have the following fields:

    EmpID

    Name

    Now create a measure to pull in the selections from this table.  It can vary depending on your needs but this should get you started:

    EmpSelection:=MAX(DimEmployee[ID])

    Next, if you create a pivot using Project[ProjectID] in Rows you should see a pivot contain an unfiltered list of all ProjectIDs.  Add a slicer to this pivot using the DimEmployee[Name] field.

    If you select an employee in the slicer, nothing happens to the list, so now you need to create a measure to filter this list.

    A basic one to get started:

    FilterProjects:=
    
    SUMX(
       Projects,
       IF(
         [EmpSelection] = Projects[DirectorID] ||
         [EmpSelection] = Projects[ManagerID],
         1,
         BLANK()
       )
    )
    This measure basically gives you a 1 if the selected Employee is either a Director or Manager and a null if not so the row will disappear from your pivot.

    You could then tweak this measure or use it in new measures to filter their values based on the selection. The 1 could be replaced with an actual measure such as SUM(Projects[ProjectCost]) for example.  Or you could define a new measure like this:

    ProjectCost:=
    
    IF(
      [FilterProjects],
      SUM(Projects[ProjectCost]),
      BLANK()
    )
    Again, there are multiple ways to go about this and depending on your needs you may have to adjust the measures so that the Grand Totals make sense in your context but this pattern should get you started.



    Wednesday, May 21, 2014 4:58 PM

All replies

  • I would probably try to use Disconnected Tables/Slicers to accomplish something like this.  There is a lot of info on them out there if you search for it.

    Basically, it involves adding a table to your data model with no relationships to other tables in your model.  This gives you a way to input parameters using slicers.

    As a simple example, lets say your Project table contains the following fields:

    ProjectID

    DirectorID

    ManagerID

    ProjectCost

    Now you would add a new disconnected table to your model called something like DimEmployee which is a unique list of all Managers and Directors.  Don't relate it to Projects. It would have the following fields:

    EmpID

    Name

    Now create a measure to pull in the selections from this table.  It can vary depending on your needs but this should get you started:

    EmpSelection:=MAX(DimEmployee[ID])

    Next, if you create a pivot using Project[ProjectID] in Rows you should see a pivot contain an unfiltered list of all ProjectIDs.  Add a slicer to this pivot using the DimEmployee[Name] field.

    If you select an employee in the slicer, nothing happens to the list, so now you need to create a measure to filter this list.

    A basic one to get started:

    FilterProjects:=
    
    SUMX(
       Projects,
       IF(
         [EmpSelection] = Projects[DirectorID] ||
         [EmpSelection] = Projects[ManagerID],
         1,
         BLANK()
       )
    )
    This measure basically gives you a 1 if the selected Employee is either a Director or Manager and a null if not so the row will disappear from your pivot.

    You could then tweak this measure or use it in new measures to filter their values based on the selection. The 1 could be replaced with an actual measure such as SUM(Projects[ProjectCost]) for example.  Or you could define a new measure like this:

    ProjectCost:=
    
    IF(
      [FilterProjects],
      SUM(Projects[ProjectCost]),
      BLANK()
    )
    Again, there are multiple ways to go about this and depending on your needs you may have to adjust the measures so that the Grand Totals make sense in your context but this pattern should get you started.



    Wednesday, May 21, 2014 4:58 PM
  • That's brilliant - thank you so much.

    Paul

    Thursday, May 22, 2014 8:33 AM
  • Mike, and any other gurus out there!!

    I've implemented the solution but now I have another couple of questions!!

    1/ I need the slicer to work when more than one value is selected. Can this be done?

    2/ I need the slicer to work in conjunction with other slicers. To continue your example above I'll add a few more columns to the table.

    ProjectID
    DirectorID
    ManagerID
    ProjectCost
    ProjectStatus
    Unit
    Division
    Subdivision

    At the moment if I clear the Employee filter then it shows me results for 99999 (the highest value in the table) and this means that it doesn't work properly in conjunction with other slicers unless someone is selected.

    There will be a case where someone might like to select a subdivision or perhaps a project status before selecting an individual.

    I could adjust the formula like this:

    =SUMX(Project,
    IF(
    [Employee ID Selected] = 99999 ||
    [Employee ID Selected] = Project[ProjectDirectorCode] ||
    [Employee ID Selected] = Project[ProjectManagerCode],Sum('Project Financials'[Total Costs]),BLANK()
    )
    )

    but it seems a bit clunky and so I was wondering if there was a way to adjust this formula?

    Employee ID Selected=MAX('Project Role'[EmployeeCode])

    to pass through a 'special value', zero pehaps, if no-one was selected.

    That way this formula could become:

    =SUMX(Project,
    IF(
    [Employee ID Selected] = 0 ||
    [Employee ID Selected] = Project[ProjectDirectorCode] ||
    [Employee ID Selected] = Project[ProjectManagerCode],Sum('Project Financials'[Total Costs]),BLANK()
    )
    )

    Obviously the solution for question two might be affected by the solution to question one.

    Thanks for any suggestions,

    Also, should I re-post this as a fresh question?

    Paul


    • Edited by Paul Trower Wednesday, June 04, 2014 1:33 PM Typo
    Wednesday, June 04, 2014 1:32 PM
  • Paul,

    I believe modifying the FilterProjects measure can solve both your issues.

    Try this instead for that measure:

    FilterProjects:=
    IF(
       CONTAINS(
           VALUES(DimEmployee[EmID]), 
           DimEmployee[EmID], 
           MAX(Projects[DirectorID])
       )  ||
       CONTAINS(
           VALUES(DimEmployee[EmID]), 
           DimEmployee[EmID], 
           MAX(Projects[ManagerID])
       ),
       1,
       BLANK()
    )

    If this doesn't work for you let me know but I think this should solve both your issues for you.

    On a side note, the EmpSelection measure can use any aggregation not just MAX(). It could have been MIN(), AVERAGE(), etc.  All will effectively give the same result if you are only allowing a single parameter selection.  The aggregation selected becomes important as you have seen when there are multiple selections.  Since MAX() was used, you get the max value of multiple selections.  You could force it to have a default value on multiple selections by using something like this:

    EmpSelection:=
    IF(HASONEVALUE(DimEmployee[EmpID]),
       [MAX(DimEmployee[EmpID]),
       0
    )
    Again, if you modify FilterProjects, as I have suggested, you shouldn't have to worry about this but since you asked, that is the answer.



    Wednesday, June 04, 2014 3:27 PM
  • Mike, that's great. I'll get started on it in a bit.

    One other 'general' question.

    <picture removed>

    I have a 'Project' table which has all the 'details' of the projects and I have a 'Project Financial' table that has all the measures. Both tables have the PD and PM employee key columns. Which is the best table to link to? At the moment I have linked to the project table but now I am wondering if I should use the Project Financial table??

    Any advice greatly appreciated (I am at the start of my journey!).

    Paul

    PS - could re-write the formulas to use the correct column names ? I wouldn't normally ask but I'm keen to see how an experienced person writes them


    • Edited by Paul Trower Thursday, June 05, 2014 10:21 AM Removed picture so that I can display two picures later in the thread
    Wednesday, June 04, 2014 3:48 PM
  • Paul, is it a one to one relationship between Project and Project Financials?

    In other words for every row in Project is there only a single corresponding row in Project Financials or are there possibly many corresponding rows for each ProjectKey?

    I'm trying to fully understand your model.

    Wednesday, June 04, 2014 3:56 PM
  • Mike, thanks again for your help. This is my first attempt at using the Excel Data Model (having part-read Rob Collie's DAX Formulas for PowerPivot Book!) and I really appreciate the advice.

    I'm now going to write a full 'brief' and the method I am going to use to tackle the problem, not in the expectation of you doing all the work but so that any suggestions you make won't need to be re-worked later. As a developer myself I know that there is nothing more annoying that not getting the full picture early on, because it tends to mean lots of changes and re-writes along the way as.

    A picture tells a thousand words so I've given you another fuller view of the tables I have at my disposal, which I hope will be helpful.

    And so to the explanations.

    It is a one-to-many relationship between project and project financials.
    Each project has one row in Project and many rows in Project Financials (one row per month).

    The Calendar table is as you would expect a date table.
    The Project Role table is the table that holds the full list of employees. Each Project has two rows, one for its project director and one for its project manager.
    The Project Delivery table has many rows for each project (one for each month).
    The Aged Debt table has eight rows per project and these correspond to eight debt descriptions in the Aged Debt table. Essentially each project has its debt split across eight bands depending on how old the debt is.
    The ExchangeRate holds currency exchanges rates for 39 currencies and Currency table holds the names of those currencies - linked by a currency key column.

    The data that I am interested in is for the 'current period', the 'previous period' and the twelve periods before that. At the moment I have manually adjusted the Project Financials, Project Delivery and Calendars table properties so that they only bring through the required periods of information:

    WHERE (([ReportCalendarDayKey] = 20130501) OR ([ReportCalendarDayKey] = 20130601) OR ([ReportCalendarDayKey] = 20130701) OR ([ReportCalendarDayKey] = 20130801) OR ([ReportCalendarDayKey] = 20130901) OR ([ReportCalendarDayKey] = 20131001) OR ([ReportCalendarDayKey] = 20131101) OR ([ReportCalendarDayKey] = 20131201) OR ([ReportCalendarDayKey] = 20140101) OR ([ReportCalendarDayKey] = 20140201) OR ([ReportCalendarDayKey] = 20140301) OR ([ReportCalendarDayKey] = 20140401) OR ([ReportCalendarDayKey] = 20140501) OR ([ReportCalendarDayKey] = 20140601))

    I know this is clunky and it will need to be automated at some point using the CurrentFiscalPeriod and CurrentFiscalYear values.

    Question: Will I be able to restrict the data imported into the Project Financials and Project Delivery table based on information in the Calendar table (which is the only place where the CurrentFiscalPeriod and CurrentFiscalYear values are stored?

    Starting at the end

    The finished product we are trying to produce will be something like this. This is not 'set in stone' but when I drew this it was the only way I could see us achieving what we need to achieve (however that was before I found out how powerful DAX can be).

    Current Methodology
    The way that I currently am doing things is by creating three pivots that look up the three fact tables (delivery, debt and financial) based on the slicer choices. I then use Excel to create some 'measures' and finally I use some lookups to display that information on a 'Dashboard' presentation sheet. I came up with this methodology before reading about DAX and it might be that I can now create the measures using DAX and avoid the need bring through the information and then 'mash' it in Excel - time will tell but I am hopeful.

    The Requirements
    What we are looking to do is create a 'dashboard' showing information about projects that belong to our employees.

    The dashboard will show KPIs for six aspects of the project. There will also then be an option to go to a 'single project' page that will show more detail when just one project is selected. As an example on the dashboard page we show the worst debt category and its amount, on the single project page we will show the eight debt 'age bands' and the amounts of debt for each age band.

    The list of projects needs to be filtered by Employee but because that list is very long (47,000 entires) we want to be able to shorten it by choosing a Unit/Division/Subdivision/Project Status first.

    Clicking on currency will adjust the values to the chosen currency. This I envisage doing by using a disconnected table 'lookup' that will multiply the values by the chosen exchange rate.

    Question - Long List of Names: rather than use the Project Role table that has all PMs/PDs since the beginning of time would it make sense/be possible to create a table on the fly from the list of names in either the Project of the Project Financials table? I have analysed the columns and got some numbers to help you come to an educated opinion.

    Project Financials = 190,000 rows, two columns PD and PM, 4281 unique values.
    Project = 17,500 rows, two columns PD and PM, 4301 unique values.
    Project Role = 48,000 rows, one column, 4821 unique values.

    As per the post above - at the moment Project Role is 'talking' to the Project Table, is this the most efficient way to do this?

    The challenges (for me)
    Is my approach right? Should I be using three pivots or should I look to create the measures using DAX (it's funny but just by writing this email I already think I know the answer!) and get them all on one pivot table.

    Assuming that the answer is to create the measures in DAX and then put them all onto one pivot table I might (for 'might' read 'almost certainly') need your help - but I will attempt to do it myself first and then perhaps get you to 'peer review' my code - if that is OK?

    My plan of action
    I think that the things that I need to iron out/do are these:
    1 - Ascertain the most efficient way of meeting the requirements. i.e. is it by creating one pivot table that brings through the project data and the KPIs and then creating two 'front-end' presentation tabs, one for the KPIs and one for Single Project analysis. Or should I create one pivot table that shows the KPIs on a 'dashboard' tab and another pivot table (or maybe multiple pivot tables/charts) that shows the single project information on a 'single project' tab.
    2 - Ascertain the most efficient way of doing the employee lookup.
    3 - Create measures for my 'KPIs' using DAX.

    How you can help
    If I have given you enough to go on I would really value your opinion on points 1 and 2 in my plan of action. I think that getting this right will be crucial to the speed that the workbook runs at (and whether or not I can meet all the requirements) - in my investigations I have quickly found that adding columns/slicers etc can suddenly slow things down beyond being useable.

    Obviously later on there will be more questions relating to restricting the import data to just the right periods and my DAX measures formulas but they I think are 'small beer' compared to the 'architectural' questions.

    I'm sorry if this post is to long but as I said at the top if you are to make intelligent suggestions then you need to see the whole picture.

    In anticipation of your help many thanks again,

    Paul



    • Edited by Paul Trower Thursday, June 05, 2014 10:22 AM Re-added second picture that wasn't showing
    Thursday, June 05, 2014 10:10 AM
  • Mike

    I don't normally double-post until someone has replied to the first post but I just wanted to say thanks for the above piece of code - it works perfectly.

    This is the code as I have implemented it:

    FilterProjects:=
    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()
    )

    I'd like to try and get straight in my head exactly what is going on in this portion of the code:

           CONTAINS(
           VALUES('Project Role'[EmployeeKey]),
           'Project Role'[EmployeeKey],
           MAX(Project[ProjectDirectorKey])

    I've read up and I know that the syntax for Contains is Contains(Table,ColumnName1,Value1) and that the syntax for Values is Values(Table or Column Name) and the return value is a one column table.

    My understanding is...
    If nothing is clicked on the slicer then:
    the first line returns a one column table where the one column has a full list of values from the 'Project Role'
    the second line tells it to use the Project Role EmployeeKey column from that table (the only one in this case)
    the third line is the bit I need clarifying/don't understand... does it go down every line in the 'Project' table and compute MAX(Project[ProjectDirectorKey]) and see if that value is in column of values returned from line two?

    And then if something is clicked on the slicer:
    the first line returns a one column table where the one column has a list of the selected values from the 'Project Role'
    the second line tells it to use the selected list of 'Project Role'[EmployeeKey} column from that table
    the third line is the bit I need clarifying/don't understand... does it go down every line in the Project table and compute MAX(Project[ProjectDirectorKey]) and see if that value is in column of values returned from line two?

    Have understood that correctly?

    One further addendum to the formula - if I wanted to compare text values in both tables could that be done? I've had a go but can't get it right.

    Thanks
    Paul

    Thursday, June 05, 2014 3:52 PM
  • Paul,

    I have read through your posts and will respond more specifically to some of your questions in the bigger one when I have a little more time.  Basically, you look to be on the right track with your model.

    I'm glad the new measure is working for you and it looks like you have modified it exactly as I would have to work with your table and column names.

    It sounds like you are pretty much right on point with your understanding of how it works.  CONTAINS() basically takes a table and looks for specific values in one or more columns and returns TRUE/FALSE depending on if the value is found.  The value can either be static or dynamic based on a measure or formula.

    The third part MAX(Project[ProjectDirectorKey]) works because that value contains numbers.  This part returns whatever the max value is for the selection.  Because your final report uses Project Number or Project Name to identify a single unique project in your pivot this is returning the max of a single row.  It could be any aggregate such as MIN(), AVERAGE(), SUM(), as long as you are looking at a single row in the projects it will be the same.  The only problem could arise is if you had the same project listed twice with different ProjectDirectorKey each time.  Then MAX() would only return the high value of the 2.

    You can use CONTAINS() with text as well, but it gets slightly trickier because you can't use any of the aggregate functions on text. Instead you must use VALUES(). As you noted VALUES() returns a single column table so used alone, it will return an error because the value arguments of CONTAINS() require a single value and not a table.  To get around this, you add a test to your measure to make sure VALUES() is only returning a single value and not a table of values:

    FilterProjects:=
    
    IF(HASONEVALUE(Project[ProjectDirectorKey]) &&
       HASONEVALUE(Project[ProjectManagerKey]),
     IF(
        CONTAINS(
            VALUES('Project Role'[EmployeeKey]), 
            'Project Role'[EmployeeKey], 
            VALUES(Project[ProjectDirectorKey])
        )  ||
        CONTAINS(
     VALUES('Project Role'[EmployeeKey]), 
            'Project Role'[EmployeeKey], 
            VALUES(Project[ProjectManagerKey])
       ),
        [Sum of Total Costs],
        BLANK()
     ),
    BLANK()
    )

    This is basically saying if either field in Project has more than 1 value, the measure returns a blank, otherwise it works for unique rows.

    Another function that might work for your scenarios is LOOKUPVALUE().  This is kind of a cross between CONTAINS() and a traditional Excel VLOOKUP().  It can be used to look for a value in an unrelated table based on a match with a value in another table or a measure.  Could be useful in your currently conversions.

    Thursday, June 05, 2014 6:43 PM
  • Mike

    Thanks for the message much appreciated.

    Paul

    Friday, June 06, 2014 10:28 AM
  • Paul,

    Here are a few follow up responses to some of your other questions:

    Overall, your design looks pretty good and you are headed in the right direction.  In general, your fact table (Project Financials) should just have columns with values your will use to build measures and FK's to link back to your dimension tables.  Don't import columns you don't need in reports or calculations.  This especially applies to your Fact tables because they are normally the largest by far.

    1. Project Role - I had been referring to this table as DimEmployee.  This table probably doesn't need a project key since it is disconnected.  It really only needs to contain a single, unique row for each employee.  Since this table is basically just a parameter table for selecting employees, it doesn't really matter which project from this table's standpoint.  It sounds like creating this on the fly is your best option.  This table should only have 1 row and EmployeeKey per employee.

    2. Calendar Table - There are many ways to automated the date range selected on import of all tables using SQL.  You could easily have it import something like the last 14 months from the current month or based off a date stored in another SQL table.  This is probably a question to be discussed in a new, separate thread.

    3. Final Report design - It looks mostly doable with Power Pivot.  The only things you will have to tweak are merged rows/columns.  This can't be done with Pivots so you will have to come up with design that utilizes additional columns instead of merged rows.  You could do something with merged cells if you built your report using Cube functions, but Cube functions work best with a static layout and may not be the best choice for this since I'm sure projects are frequently added and deleted.  There are many blog/forum post related to building reports with Cube functions.

    4. Design decisions - Using one pivot or multiple? Doing as much as you can with DAX measures?  There is not necessarily a right or wrong answer to this.  Whatever works best for your current project and needs.  It will always vary from project to project.  You have to consider usability and performance when making these decisions so sometimes it is best to try multiple ideas and see what works best on this particular project.

    5. KPI's - I'll show you the pattern that I would use to build one of your KPI's.  I'll use Total Cost since you did in your example but the pattern will pretty much be the same for most of them.

    This will involve a couple base measures and then the actual measure to use in your reports:

    1. The original filter measure that filters projects based on Employee selection:

    FilterProjects:=
     IF(
        CONTAINS(
            VALUES('Project Role'[EmployeeKey]), 
            'Project Role'[EmployeeKey], 
            MAX(Project[ProjectDirectorKey])
        )  ||
        CONTAINS(
     VALUES('Project Role'[EmployeeKey]), 
            'Project Role'[EmployeeKey], 
            MAX(Project[ProjectManagerKey])
       ),
        1,
        BLANK()
     )

    2. Basic sum of Total cost referencing measure 1:

    SumOfTotalCost:=
    
    IF(
      [FilterProjects],
      SUM('Project Financials'[Total Costs]),
      BLANK()
    )

    This measure will work properly for individual projects but depending on your report layout it may not give the right amounts for subtotals or grand totals, so the next measure should give proper totals in most layouts

    FinalTotalCost:=
    
    IF(
      HASONEVALUE(Project[ProjectKey]),
      [SumOfTotalCost],
      SUMX(
        Project[ProjectKey],
        [SumOfTotalCost]
      ),
    )

    This should give correct subtotals and grand totals and does not require the 2 base measures to be in the pivot to work properly.

    My final tip would be to ask specific questions on new topics in new thread posts.  You'll get more views by others by posting new, unanswered threads.  You could always link back to this one for a point of reference.  I think you get the most community help by keeping threads specific to a single question or issue.  Once that has been answered, move onto a new post.

    Monday, June 09, 2014 3:33 PM
  • Mike

    Thanks for your answers / comments - all very useful.

    I agree with your 'one post per issue' idea and I will create some new threads for the bits that I have to do - watch out for a post relating to the project manager / director lookup - the lookup is working fine but the other slicers aren't highlightling the appropriate choices.

    Paul

    Monday, June 09, 2014 3:43 PM
  • Mike

    I know that I was going to start other threads but this seemed more relevant to just ask you.

    1. Project Role - I had been referring to this table as DimEmployee.  This table probably doesn't need a project key since it is disconnected.  It really only needs to contain a single, unique row for each employee.  Since this table is basically just a parameter table for selecting employees, it doesn't really matter which project from this table's standpoint.  It sounds like creating this on the fly is your best option.  This table should only have 1 row and EmployeeKey per employee.

    How would I go about this? Are you talking about creating a measure using VALUES() to store these values?

    Thanks
    Paul

    Monday, June 09, 2014 3:54 PM
  • I would do this on import with SQL Group By.

    Leave out the ProjectKey and group the Project Role table by EmployeeKey, Name, etc.  Whatever fields are unique to a specific employee.

    This technique can also be used to help make large slicers more useable:

    http://www.powerpivotpro.com/2012/12/initializing-slicers-with-too-many-values-to-scroll/

    Monday, June 09, 2014 4:34 PM