Using a Slicer to Indirectly Filter a Line Chart

[Anonymous]

I have a scenario I need help with.

BACKGROUND

I have a table of data that contains the following columns.

Project

Person

Date

Hours Type

FTE

There are two slicers. These slicers are set to intractact with eachother. The two slicers are:

Person

Project

When I select a Person, the Project slicer shows only the projects assigned to that person.

When I select a Project, the Person slicer shows only the people assigend to that project.

I need to plot the sum of the FTE on a given day based on the values of the slicers. (Please see the attached image)

Here is my PROBLEM

There are many projects where the FTE on a given day is euqal to zero. There are some projects where the FTE is zero on all days. When this is true, I don't want those projects to be listed in the slicer. To do this, I used a filter on the slicer to only list a project where the FTE is greater than zero (see the attached picture).

Also, I don't want those projects to be plotted on the line chart. The problem is, if I use the same filter on the line chart, where FTE is greater than zero, I don't get the result I want. While the list of projects in the line chart is correct, the line chart no longer plots the days when the FTE is zero.

If I make selections in the project slicer it works because only the projects that are selected will plot. But I need it to work when I make selections in the person slicer. For example, if I select Ryan in the person slicer I want to see only the projects assigned to Ryan in the project slicer where the FTE is greater than zero. I also need the line chart to plot those same list of projects (the ones that are showing in the project slicer) but I need it to plot all the days. Even the ones where the FTE is zero. But if I use the same Filter on the line chart (where FTE is greater than 0), those days don't plot.

What I need to be able to do is limit what is on a line chart based on what is in the project slicer when the what is lsited int he project slicer is being controlled by the person slicer. 

[d_gosbell]

You may have to upload another example file, I'm not really sure what you are seeing or why. When I applied the fix to your example file I don't think it changed the tooltips at all.

[Anonymous]

@d_gosbell 

Here is a link to the new file.

And here is a link to an image of what I am seeing.

Sorry for the late replay. I have been on holiday.

[d_gosbell]

I assume you are talking about the Resouces page in your new file? In the bottom chart on that page you have explicitly added FTE_Check to the tooltip field well. If you remove that measure from the Tooltip I think this will fix your issue.

[Anonymous]

Oh, geez! What a rookie mistake. Thank you.

[Anonymous]

My follow up question below is probably related to your explanation above. When I hover over a line in the line chart to get detailed data, I know get a mess of data. I used to get the project name and FTE associated with the project. Now I get three entries for each line.

Project [Project Name Value]
[Project Name Value] 0.00
FTE_Check [FTE Value]

Where the text above in the square brackets is reaplaced with the real data. Note: 0.00 never changes. It always says 0.00

Any thoughts on why this is happening and how to get back to what I had before?

[Project Name Value] [FTE Value]

[d_gosbell · Accepted solution]

Ok, so the issue here is because your data model is different to what you described in your first post. In there you only mentioned a single table, so I shared code that would work with a single table, but because you have a separate calendar table and columns from that are on the axis of your graph you should create a measure like the following and use that to filter your slicer and graph

FTE (all time) = CALCULATE( SUM( 'Master Filtered'[FTE] ) , ALL('Calendar'))

[Anonymous]

@d_gosbell That did it! Thank you.

I'm not usre I understand how this works. Could you explain to me what this measure is doing? How does it filter out the projects that have no FTE > 0?

[d_gosbell]

---

@Anonymous wrote:

I'm not usre I understand how this works. Could you explain to me what this measure is doing? How does it filter out the projects that have no FTE > 0?

---

It's basically calculating a grand total of FTE over all dates. When you calculate this on a line graph that has a line per project it will calculate the grand total per project.

If you create a new test page in your report then create a table visual that has project, date, FTE and the new [FTE (all time)] measure you should be able to see what it's calculating

[Anonymous]

@d_gosbell 

That worked on the slicer. It gives me the same behaivor as if I just use a filter on the project slicer to be when FTE is greater than zero. However, it still doesn't fix the line chart it still doens't lot any zero values.

I need the following behavior from the line chart.

1) For a project where all the FTE in a date range add up to be zero, don't plot any values. I don't even want the project to show up as a series on the chart. It shouldn'ty be listed in the legend. I want it to be as if those projects don't even exit.

2) For a project where all the FTE in a date range add up to be greater than zero I want them to be plotted. But I also need the days where the FTE is zero to be plotted as zero. Right now, becasue the filter excludes anything that isn't greater than zero, it just treates those days that should be zero as missing data. It just connectes the FTE from the day before to the day after.

Another way of saying this is:

1) I want to exclude any series (project) where the sum of all FTE is 0

2) I want to include all other series (projects) and plot all FTE for those projects, even the ones equalt to zero

[d_gosbell]

---

@Anonymous wrote:

That worked on the slicer. It gives me the same behaivor as if I just use a filter on the project slicer to be when FTE is greater than zero. However, it still doesn't fix the line chart it still doens't lot any zero values.

---

Oh hang on, are the values 0 or blank? Because blanks are always eliminated from visuals. If the values are actually blank not 0 one possible fix is to make a new measure:

[FTE (for chart)] = SUM( Table[FTE] ) + 0

If this does not solve your issue can you post an example pbix file or even just 5-10 rows of example data which exhibit this issue? 

[Anonymous]

Here is a link to my file.

If you go to page 3 of 5 (the one named Projects) you will find my problem.

In order for the projects filer to show only the projects that have FTE hours in the date range selectred in the Date slicer on the Slicer page, I used a filter where FTE is greater than Zero.

In order to have the same list of projects in the line chart below the Projects slicer, I used the same FTE > 0 fitler. The probelm is, this gives me the right list of projects, but it also doesn plot any values where FTE = 0 for those projects. None of the projects have FTE > 0 on 2/7 and 2/14. This can be seen in the AVL/PLN line chart above the Projects line chart. PLN FTE hours on 2/7 and 2/14 are zero.

[d_gosbell]

So you should be able to do this by creating a measure that calculates the total FTE across all dates and then check if this is > 0 in your chart. The expression to calculate a total FTE across all dates would look like the following:

Total FTE = CALCULATE( SUM( Table[FTE] ), ALL(Table[Date]) )

[Anonymous]

@d_gosbell Thank you. I think I understand. Will this make it so that only the projects that sum to zero don't display? If a project has even one day where the FTE is greater than zero I want it to be in the list that is ploted. If it has no days I don't want it in the list that is plotted.

I don't see how proejects are a part of your equation above.

[d_gosbell]

---

@Anonymous wrote:

@d_gosbell Thank you. I think I understand. Will this make it so that only the projects that sum to zero don't display? If a project has even one day where the FTE is greater than zero I want it to be in the list that is ploted. If it has no days I don't want it in the list that is plotted.

---

Yes, it's basically counting the FTE for ALL dates

---

I don't see how proejects are a part of your equation above.

---

Because I'm only using the ALL function over the date column any filter on any of the other columns will remain active. That way you could use this same measure for graphs on person or hour type.  

Or if you only every wanted this calc to work across different projects instead of ALL( Table[Date] ) you could do ALLEXCEPT( Table[Project] ) which would keep any current selections on Project but strip them off all other columns.