Filters with no data

[marcelodiazm16]

Hello! I have a problem, i have two filters (month and year) and the data im working on starts from june 2023 until february 2024 (now), so i don't want to show, for example, the months with no data (because it will appear [blank] in the cards) when i select a year.
i mean, when i select 2023, i want to the other filter with the months to show me the only months with data. 

Please help me.

Thanks!

[Anonymous · Accepted solution]

Hi @marcelodiazm16 

Unfortunately, it's not support to dynamically change the field of the filter.

But maybe you can try this:

I create a set of sample data:

It is easy to see that the value of Jan, Feb, Mar is empty.

Then Create a calculate column:

Month = IF(
        ISBLANK('Table'[value]),
        BLANK(),
        MONTH('Table'[date])
    )

Then create a Slicer:

The result is as follow:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[bhanu_gautam]

@marcelodiazm16 , For this you can create new calculated column using below mentioned formula

FilteredMonths =
VAR SelectedYear = SELECTEDVALUE('YourDateTable'[Year])
RETURN
FILTER (
VALUES('YourDateTable'[Month]),
CALCULATE(
COUNTROWS('YourDataTable'),
'YourDateTable'[Year] = SelectedYear,
'YourDateTable'[Month] = 'YourDateTable'[Month]
) > 0
)

Please accept as solution and give kudos if it helps

[marcelodiazm16]

Mmmm it didn't work, it showed me (blank) in month options, but i know there is an easy way, i just don't remember 😞 Thank you though