Dynamic Month Filter based on Year Filter selection

[nareshr89]

I have two dimension tables which i use as filters, Year filter and Month filter.

When i select a specific year like 2022 or 2023, then month filter should show only December and in the remaining cases it should show all the months.
Any suggestion would be of great help..!

[AnalyticsWizard]

@nareshr89 

You can achieve this by creating a new calculated column in your Month table that depends on the selected value in the Year filter. Here’s a step-by-step guide:

1. Create a new calculated column in your Month table. You can name it FilteredMonth.
2. Use the SELECTEDVALUE function to get the currently selected year. This function returns the value when there’s only one value in the specified column, otherwise, it returns an alternative result.
3. Use an IF statement to check if the selected year is 2022 or 2023. If it is, then check if the month is December. If it’s not, then return all months.

Here’s a sample DAX formula for the calculated column:

FilteredMonth = 
    VAR SelectedYear = SELECTEDVALUE('Year'[Year], "All")
    RETURN
        IF(
            SelectedYear IN {"2022", "2023"},
            IF('Month'[Month] = "December", "December", BLANK()),
            'Month'[Month]
        )

In this formula, if the selected year is either 2022 or 2023, it checks if the month is December. If it is, it returns “December”, otherwise, it returns a blank value which effectively filters out the other months. If the selected year is not 2022 or 2023, it returns all months.

After creating the FilteredMonth column, use it in your visuals or tables instead of the original Month column. Also, make sure to use this new column in your Month filter.

Please note that this solution assumes that you have a single select filter for the Year. If you allow multiple selections for the Year filter, you might need to adjust the formula to handle multiple years. Also, remember to refresh your data after making these changes to see the effect.

I hope this helps! Let me know if you have any other questions. 😊

[lbendlin]

Calculated columns can't (should not) be created from measures or filters.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?