Combined date slicer : how to extract and use parts of it to affect visuals differently

[Matt_VBC]

Hello !

I have a page with two sets of visuals : cards and graphs. The cards contain raw and computed KPIs with added MoM and YoY values for context interpretation. The graphs are barebones representation of each KPI's monthly trend (so "Month" is X).

On the same page I also have a slicer based on a MonthYear column (e.g. 'March-2024').

My goal is to have this one slicer affect those two sets of visuals differently : the month and the year (so the whole slicer) affect the cards but only the year should affect the graphs.

I know it is possible to achieve this result by having two slicers (a MonthYear one and a Year one) and playing with interactions as it is what I currently did (see image included). However having two filters, one for each set of visual, could be confusing and for user experience's sake I'd want to reduce that number.

I attempted to do that with measures by having one extract the year value from the slicer and then using that as a base to compute the values that need to be shown but I always run into some form of limitation (one being that measures are calculated when you open the report and not when a user is changing the value selected in the slicer). I also learned that measures shouldn't be used to return more than one value.

I'm new to Power BI so I still have a lot of blindspots and the solution might be simple.

Is there any way to achieve the result I want without using two slicers ?

[Aburar_123]

Hi @Matt_VBC ,

You can achieve it as below,

dont create a relation between Date Dim and Orders (your source table). these two tables should be independent. then write below two measures,

Sales for Selected YearMonth = CALCULATE(SUM(Orders[Sales]),FILTER(Orders,FORMAT('Orders'[Order Date],"mmm")&"-"&YEAR(Orders[Order Date])=SELECTEDVALUE('Date Dim'[Year_month])))

Use the above measure in the card

Year Selected = IF(MAX(Orders[Order Date].[Year])=SELECTEDVALUE('Date Dim'[Year]),1,0)

Use this measure in the filter of the line chart as below,

In this case i have used the Order Date column in the X-axis of line chart and slicer is coming from Date Dim table

[Matt_VBC]

Hello @Aburar_123 

Thanks for the reply ! If I understood it correctly this trick with measures recreates artificially what relationship does but with more control, right ?

I have two questions :

1. I'm not sure how the Year Selected measure works, especially when used in the filters panel. Could you explain its logic ?
2. In a professional context isn't it always mandatory to have a relationship between the dimension table and whatever fact table ? Therefore : is there a solution to my problem that exists with that relationship ? So far I managed to make it work with two filters (and normal relationship between datetable and fact table) and also with your workaround (without relationships).
   

Thanks again !

[Aburar_123 · Accepted solution]

Hello @Matt_VBC ,

Yes. Please find the answers below,

1. MAX(Orders[Order Date].[Year]) is the year of each date value in the X-axis of the line chart.

SELECTEDVALUE('Date Dim'[Year]) is the year of the date/Month-Year value selected in the slicer.

this measure is applied in the line chart where it checks the year of each date value in the X-axis with the slicer selected Date's year, so the Date value in the X-axis will display it if the measure produce the value 1 otherwise not.

2. It is based on the requirement. your requirement can be achieved only if there we keep the tables independent because you need a different granular data. ie, Slicer is in Month level but you need of data one level up at year level that give you more than one record for each slicer selection.

Please Accept as a solution if my post solves your problem

[v-ssriganesh]

Hello @Matt_VBC,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop using a single Month+Year slicer and I got the expected output as per your requirements.

• The card visuals reflect metrics filtered by the full Month+Year (e.g. "July-2024")
• The line chart visuals display the monthly trend for the entire year (e.g. 2024), regardless of the selected month
• This was done using DAX measures that extract the Year from the slicer, allowing the same slicer to drive two different behaviours
• Additionally, slicer interactions were turned off for graph visuals to avoid direct visual filtering

For your reference, I’m attaching the .pbix file where everything is implemented step-by-step. Feel free to adapt it to your dataset.

Best Regards,
Ganesh singamshetty.

[Matt_VBC]

Hello @v-ssriganesh !

Thanks for the reply.

I opened your file and it I'm running into the same problem when I attempted this type of solution : the graph displays one specific year (here 2024) and it is unresponsive to whatever selection is made using the slicer, meaning if I change the selection from let's say July-2023 to November-2025 the graph doesn't show the monthly trend on the 2025 year but stays on the same year.

Additionally, I witnessed the following details :
- the graph you made only goes from December to July for some reason
- the cards you included that contain the measures "NewClients_SelectedMonth" and "NewClients_MoM_Change" sometimes return values and sometimes not without a clear pattern. The expected behavior is that only the MoM should be empty in case a value in 2023 (the earliest year) is selected as no data precedes this date. NewClients_SelectedMonth should always return something if a value is selected in the slicer, I don't understand why here it doesn't.

[Greg_Deckler]

@Matt_VBC Calculated columns are calculated when refreshing a report. Measures are dynamic based on user interaction. What you want is a Complex Selector: The Complex Selector - Microsoft Fabric Community