Between slicer showing more dates than data

[Justas4478]

Hi, I have data from couple agencies that are tracking working hours.

I have made simple calendar to use in the report.

However when I when I add calendar in to slicer it shows all dates available in it even those that I dont have any data for.

For example my data starts from 23 Jan 2023, but the calendar showing date in slicers from as early as 2014 till final date avaialble in calendar 2029.

I dont know what I did wrong since I was expecting slicer to only show dates where data is available.

This is realations connections. I connected dates from different agencios to the calendat DATE column.

 

I dont know needs to be done for it to works as I expected it to.

Thanks.

[v-yanjiang-msft]

Hi @Justas4478 ,

According to your description, here's my solution.

Create a measure:

Measure =
VAR _min =
    MIN (
        MIN (
            MINX ( ALL ( 'co agency' ), 'co agency'[Date] ),
            MINX ( ALL ( 'pr agency' ), 'pr agency'[Date] )
        ),
        MINX ( ALL ( 'sp agency' ), 'sp agency'[Date] )
    )
VAR _max =
    MAX (
        MIN (
            MAXX ( ALL ( 'co agency' ), 'co agency'[Date] ),
            MAXX ( ALL ( 'pr agency' ), 'pr agency'[Date] )
        ),
        MAXX ( ALL ( 'sp agency' ), 'sp agency'[Date] )
    )
RETURN
    IF ( MAX ( 'Calendar'[DATE] ) >= _min && MAX ( 'Calendar'[DATE] ) <= _max, 1 )

Put the measure in the slicer visual filter and select show items when the value is 1. Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Justas4478]

@v-yanjiang-msft Hi, your solution partially works. It filters data as I expect but the data displayed on the calendar is wrong even tho data is filtered correctly. Data is up to 28/07/23 but the calendar i showing till 15/07/23 even tho it filters till 28/07/23 which is missleading. I dont know what is causing this interaction.

[v-yanjiang-msft · Accepted solution]

Hi @Justas4478 ,

I make a mistacke, change the MIN where I framed with red lines to MAX.

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Justas4478]

@amitchandak I tried to do as per comment but that did not work for me.
I upload a sample file. https://we.tl/t-1rRG5e9lVO 

[amitchandak]

@Justas4478 , Create a measure on fact( Meausre not column) and use that visual level filter slicer and chck for not blank