Date slicer working on data but not on axis

[bathnesresearch]

Hello clever people!

I'm trying to create an 'active cases between a date' calculation and slice it by date ranges. I have got most of the way there, but am stuck on the final hurdle. 

I successfully followed the helpful instructions outlined in https://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggrega...  https://powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbe...

and have got myself to a position where a measure (CountIfOpen) is correctly returning a count value for each relevant period. 

I have also got a working chart showing the monthly figures using the DateTable[Date] field as the x axis (set to the date heirarchy) and CountIfOpen for the value in my data. 

I have then added a slicer to the report also using the DateTable[Date] field, which is where my problem arises. The data is slicing correctly, however the X axis still displays the full date range.

For ref, the DAX for the measure looks like. 

CountIfOpen = CALCULATE(
DISTINCTCOUNT(MyTable[ID]),
FILTER(
MyTable,
MyTable[StartDate] <= LASTDATE(DateTable[Date]) &&
MYTable[EndDateCorrectedForMonthCount] >= FIRSTDATE(DateTable[Date]) 
)
)

I think what's going on is that the filter applied in the measure is overriding the slicer for the axis display, but not for the data display. 

Any thoughts/assistance would be much appreciated. 

any help would be much appreiciated. 

[c_cook28954]

I just ran into this issue myself - the best solution I found for the issue was:

1. Clicking Format your visual

2. Clicking X-axis

3. Clicking fx next to min and setting this value as the earliest date in your lookup table

4. Clicking fx next to max and setting this value as the latest date in your lookup table

When you adjust the date slicer it then adjusts the x-axis value on the table.

Edit*

This solution solved my problem in the local dataset but when I combined the dataset with a secondary dataset I ran into the same issue and the solution I previously mentioned didn't work. 

The solution didn't work because, for some reason, the min and max weren't picking up that the data type of the date was, in fact, date meaning that it kept defaulting to count suggestions instead of earliest or latest. 

In the end, I found that when I was using a measure based on a measure (ie Gross profit = Sales-COS) the date slicer wouldn't work. However, if I adjusted the measure to calculate each part of the calculation without relying on separate other measures, the date slicer would slice the data and the axis with no issues. 

My result was a massive measure with five or so variables which is not ideal but I achieved the end solution. 

*Edit 2

Make sure your measure isn't wrapped in aCOALESCE or iferror function as well - this can cause issues too

Notes: 

* I'm using a calendar lookup table separate to the date stored in the table

* Both datasets that were combined had a calendar lookup 

* All my datatypes were correctly defined

[Minniemish]

Thanks this worked perfectly for me

[Eric_Zhang · Accepted solution]

@bathnesresearch

What if setting the date instead of date hierarchy? I'm not able to reproduce your issue, could you share any sample data to reproduce it? Or even better, you can upload the pbix file in some web drive like Onedrive and share the link in a private message, do mask sensitive data before uploading.

[bathnesresearch]

@Eric_Zhang

Setting the date worked, I've no idea why 🙂

I then had to add a filter column in the date field to return an effective day field so I could isolate a single figure for that month rather than use the heirarchy. Which works better for my use case than using an effective average anyway.

thank you very much!