Forum Discussion

Justas4478's avatar
Justas4478
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Between slicer showing more dates than data

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.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      Justas4478
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      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's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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.