Forum Discussion
Date filter does not update x-axis when using date hierarchy
Hi there,
I have a line and clustered column chart and a date filter. The same date column called "Initiation Date" is used for both the date filter and the x-axis of the chart.
When using the date hierarchy option to format the x-axis, the date filter correctly filters the data, but does not dynamically filter the x-axis (please see screenshots below for comparisons).
Does anyone know how to fix this?
Date hierarchy:
Not using date hierarchy:
Hi MikePowerBI ,
I think this is caused by your INVs Initiated and Avg.INVs per Batch returning 0 values (or not blank) for the filtered out dates section.
For example, if I use the following measures separately as values for the chart, one will filter the x-axis correctly, while the other will not be filtered because January, February, March... have a return value of 0.
Note: This situation exists between the date slicer of type "Between" and the date hierarchy.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi MikePowerBI ,
I think this is caused by your INVs Initiated and Avg.INVs per Batch returning 0 values (or not blank) for the filtered out dates section.
For example, if I use the following measures separately as values for the chart, one will filter the x-axis correctly, while the other will not be filtered because January, February, March... have a return value of 0.
Note: This situation exists between the date slicer of type "Between" and the date hierarchy.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-kkf-msft thank you for your response and for the detailed explanation!
I did some troubleshooting and found that my Avg. INVs/Batch measure was causing the problem because it was written as Measure = IF(X/Y = BLANK(), 0, X/Y). This is because I am using the measure for a data card visual on another dashboard where my client does not like to see "BLANK()" on the data card.
I created another measure without the if statement and this resolved the issue. Thank you again!
I'm guessing you haven't created a date dimension table and it's automatically building one for you. I'd recommend creating one and using that dimension table for your slicer instead.
I recommend reading this for more information:
Hi AlexisOlson thank you for your reponse and for providing the great resource.
