Help Needed with Time Axis Filtering in Power BI

[Uhrond]

Hello everyone,

I'm currently facing a challenge in Power BI and would appreciate your assistance.

I have a time axis on a chart displaying data for the years 2024 and 2025. Additionally, I have a slicer that contains dates. To prevent the slicer from filtering down to a single date on the chart, I have disabled the interactions between the slicer and the chart.

However, by doing this, all dates are displayed on the X-axis of the chart, which is not the behavior I want. Ideally, when a date in 2024 is selected, only the dates for 2024 should appear on the X-axis, and the same should apply for 2025.

To achieve this, I created the following measure and filter the graph with it :

X_AXIS_GRAPH = 
VAR ANNEE = YEAR(SELECTEDVALUE('Table'[DATE])) 
RETURN IF( CONVERT(YEAR(MAX('Table'[DATE])), INTEGER) = ANNEE, 1, 0 )

When I hardcode the year (e.g., using 2024 or 2025), the chart behaves as expected. However, when I attempt to retrieve the year from the selected date using SELECTEDVALUE, it doesn't work as intended. I suspect this is due to the disabled interactions.

Does anyone have any ideas on how to achieve the desired filtering effect while keeping the slicer and chart interactions disabled ?

Any insights or suggestions would be greatly appreciated!

Thank you in advance for your help!

MyTable :

13/12/2024 -	1
13/12/2024 -	25
20/12/2024 -	4
20/12/2024 -	3
27/12/2024 -	2
27/12/2024 -	5
03/01/2025 -	6

[danextian · Accepted solution]

Hi @Uhrond 

You'll need to use a disconnected table for the slicer and create another measure that references a column from that disconnected table. If you use a column from a related table, it will only return the rows that have been selected.

Please see the attached sample pbix

[Sergii24]

Hi @Uhrond, the reason of your problem is a use of SELECTEDVALUE() function. As you can find in its definition, it expects only 1 value in a specified column: "Returns the value when there’s only one value in the specified column, otherwise returns the alternate result." - SELECTEDVALUE – DAX Guide

Let's break down your case in 2 parts:

1. You have a slicer and a bar chart that interact between themselves. In such situation, if you select a single date is a slicer, you should get the desired result, because SELECTEDVALUE('Table'[Date]) will return a single value from where you can retrieve the year. However, if you select multiple days or remove any filtering from slicer what is a single value of column [Date]? Correct, there is no one. Therefore, Power BI evaluates alternate result, which is not specified in your case, therefore it's blank(). And the Year( Blank() ) = Blank()
2. interaction between slicer and visual is turned off: in such situation you get the same problem described above, because all dates from Table are selected for bar chart.

To resovle the problem, you can replace SELECTEDVALUE() with MAX() or MIN(). This will assure that even when multiple dates are selected, only one of them is considered when calculating a year.

Good luck with your project! 🙂

[Uhrond]

Thank you for your response. Unfortunately, even with MAX(), it does not work as I would like (I think it takes the MAX() of each date and therefore each year.) :

[Sergii24]

I agree with @danextian, you'd need to go with disconnected date table.

Or simply add slicer on year instead of date 🙂