Date filter not applying to graph with measure

[Anonymous]

Hello,

I'm running into a strange issue while applying a filter to a graph of values of a measure. This is the measure in question:

AvailableUnits = 
var CommittedUnits = SUM('Engagements Timephased'[CommittedMaxUnits])/SUM('Date'[WorkDay])/100
return CALCULATE(COUNT(Resources[ResourceName]),Resources[ResourceIsGeneric]=false())-[CommittedUnits]

(note, 'Date'[WorkDay] is a column in a date table where any non weekend/holiday = 1)

 In my report, I have the following bar chart where I am graphing  'Date'[Date] vs [AvailableUnits] and want to be able to use a slicer to choose the date range to present. The slicer does include an interaction to filter the graph, but I've also directly applied the filter to the graph without success:

 If I filter on the "Date - Year" or "Date - Month" fields that are automatically included when the date hierarchy was added, the filter applies correctly:

If I only return the "CommittedUnits" portion of the measure, the date slicer works as expected:

Any suggestions on how to correct this would be greatly appreciated. Since there is no reference to the date in the measure and no all() term that could drive it to return values for all dates, I'm kind of stumped.

Thanks ahead of time.

[Ashish_Mathur]

Hi,

There seems to be some problem with the relationship.  Share the link from where i can download your PBI file.

[Anonymous · Accepted solution]

Hi Ashish,

Thanks for the offer and the guidance. I've cleaned up a copy of the file here (sorry for using google drive - my personal onedrive was frozen 😀😞 link 

Your recommendation to look into the relationships helped me get part of the way there by updating a relationship to be bidirectional which resulted in the view below. This view doesn't really work for me because it hides any of the Department Names which don't have data in a presented period.

I'm struggling to understand why I would need to make the 'Resources' - to - 'Engagements Timephased' relationship bidirectional for the date filter to work properly. It doesn't seem like it is tied to the date hierarchy in some way.

That update reminded me I've run into a similar issue in the past so I turned off the date hierarchy and set the relationship one-directional which caused the filters to apply correctly:

This led me to test using Year and Month fields calculated in the date table rather than the auto-generated hierarchy... this also had odd results where the graph would filter correctly, but only if the date hierarchy was in the field list. As you can see, there are no filters applied directly to the graph from that table which could have caused issues:

Here's a view after I've removed the date hierarchy. For some reason it skips every date where there are values for Committed Units:

The second to last image is ultimately what I needed so I guess my issue is solved, but I'd like to understand what could be the source of these issues and if there's a way around this date field quirk in case I run into them again.

Thanks,
David

[Ashish_Mathur]

Hi,

The crux of your lenghty message is that your problem is solved.  If not, then please summarise your problem showing your expected result.

[Anonymous]

Fair enough, I'll mark this complete and will open a new thread as I look into the new issue that I found with the date field. Thanks again for the help and the quick replies!

[Ashish_Mathur]

You are welcome.