Day filter problem

[fmancinelli]

Hello everyone, I need your help to solve a problem I have in the filter pane.
First I explain that my dataset has information of 4 years, that's why you will see in the image that the Year filter has a 1/4 proportion (progress bar). If you see the Month filter, you can also see that it is proportional to 12 months. But the Day filter does not show me from 1 to 31, but repeats the days for each record in the dataset. And instead of having 1/31 it shows 1/960.

The data model is:

And if I check the view of the source table "MasterCalendar" at the time of the display on the sheet, this is what I see:

So if the data to be selected correspond to 29 days, why does the Day filter show 960 days?

Thanks

[Or]

Filters show all values, not just the ones that correspond to your selection. Values that are excluded by your selection will show up in dark gray, but they'll still show up.

As to why there are 960 values for "Day" in your data set, that's hard to guess at from the information you've included. Perhaps this is a case of someone not deriving the day correctly, for example - using Date(Field, 'd') instead of using Day(Field)?

[fmancinelli]

Thanks @Or for your answer. The field in the master calendar table is created as follows: Date(Floor(TempDate), 'D') AS [Day]

I understand what you mention about excluded values in the selection. But I have had in front of me a sheet that does the same filter that I do, and it only has 31 values the Day filter. I know it can be done, I just don't know how....

[oskartoivonen · Accepted solution]

The script used to generate the Day-field isn't doing exactly that you were hoping for.

Your current solution "Date(TempDate, 'D') as Day" simply changes the display string for each date, showing the day number for said date. But the underlying value is still the actual date value e.g. 44774 for 01/08/2022. This means each first date of the month will display as an unique "1"-value in your filter pane, which is understandably quite confusing.

Try using this script to generate the Day-field: Day(TempDate) as Day

[fmancinelli]

Hi @oskartoivonen , this is the solution. Thanks a lot!

Regards