Forum Discussion

pva's avatar
pva
Frequent Visitor
4 years ago
Solved

Date filter

Hi all! I have a table with different sprints with their own star and end date. I want to filter the table like this: if I choose december 2021 I wanto it to show all the sprints that started or fini...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Hi Pedro have a look at this demo.

     

    I used a disconnected date table.

     

    Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE (2020, 1, 1), DATE (2022, 12, 31) ),
    "Month Year", DATE ( YEAR([Date]), MONTH([Date]),1) //Format as MMMM YYYY
)

    Set both of them as Date type and then set format of Month Year to MMMM YYYY

     

    Created a measure in the Sprint table:

    Show Row = 

VAR DatesInContext = 
    VALUES ( 'Calendar'[Date] )

VAR StartDates = 
    TREATAS ( 
        VALUES ( Sprint[Start date] ),
        'Calendar'[Date] 
    )

VAR EndDates = 
    TREATAS ( 
        VALUES ( Sprint[End Date] ), 
        'Calendar'[Date] 
    )

VAR AllDates = 
    UNION (StartDates, EndDates)

VAR CountDateIntersect = 
    COUNTROWS(
        INTERSECT ( DatesInContext, AllDates )
    )

VAR Result = 
    IF ( CountDateIntersect > 0, 1, 0 )

RETURN Result

     

    Created a table visual and added the measure as a visual level filter:

     

     

parry2k's avatar
parry2k
Super User
4 years ago

bcdobbs Awesome for doing the stress test, if you can share the large dataset you tested against, I have further ideas to improve the performance. Good one!

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Go for it! Dataset is nothing very fancy:

     

    BigSprint = 
VAR BaseTable = GENERATESERIES( 1, 10000000, 1 )

RETURN 
    GENERATE (
        BaseTable,
        VAR BaseId = [Value]
        VAR StartDate = DATE (2020, RANDBETWEEN( 1, 12 ), RANDBETWEEN( 1, 28 ) )
        VAR EndDate = StartDate + RANDBETWEEN(2,60)
        RETURN 
        ROW (
            "SprintId", BaseId,
            "Start Date", StartDate,
            "End Date", EndDate
        )
    )

     


    File available here.

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      Cracked it! Good lesson in making the data model do the work rather than the DAX. Analysis service never ceases to amaze me...

       

      Took same base table with 10000000 rows and expanded it out to the day granualarity. Eg each SprintId had a row per day. Could do it in powerquery or ETL but I used generate in DAX:

      SprintExpanded = 
    GENERATE (
        BigSprint,
        DATESBETWEEN('Calendar'[Date], BigSprint[Start Date], BigSprint[End Date] )
    )

      (took my laptop a fair amount of time to crunch and resulting table could do with columns being tidied)

      You can then leverage a normal relationship:

       

      No need for a visual level filter and I'm getting an order of magnitude smaller. 695ms

       

      Sorry pva the intial code from either myself or parry2k  is absolutely fine. This was just fun.

      • parry2k's avatar
        parry2k
        Super User
        4 years ago

        bcdobbs That's exactly what I was going to do, expand the table. Nothing is better if you get the data in the granularity in which you want to view your report. I will still give a few more ideas to DAX. You have done awesome bcdobbs . Thanks for sharing all this.

         

        Best,

        P

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    pva  try using a measure like this. bcdobbs  used your pbix

     

    Measure = 
VAR _yr =
    VALUES ( _calendar[Year] )
VAR _mo =
    VALUES ( _calendar[Month] )
VAR _ret =
    CALCULATE (
        MAX ( Sprint[Sprint] ),
        FILTER (
            Sprint,
            (
                YEAR ( Sprint[Start date] )
                    IN _yr
                        || YEAR ( ( Sprint[End date] ) IN _yr )
            )
                && (
                    MONTH ( Sprint[Start date] )
                        IN _mo
                            || MONTH ( Sprint[End Date] ) IN _mo
                )
        )
    )
RETURN
    _ret