Forum Discussion
Filters with no data
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!
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.
3 Replies
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
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
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.
