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:

     

     

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

Nice solution parry2k !

I think yours is potentially faster! Tempted to find/generate a large dataset and test.

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

I suspect totally academic (but felt like nerding it up) unless the dataset is massive but with a random 10,000,000 row table:

My original DAX ran in 9674ms, parry2k ran in 10267 ms which I'd count as virtually identical in terms of user experience.

 

Best I've been able to achieve is the following modification to my code which trims it down to 8491 ms.

 

Show Row = 

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

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

VAR AllDates = DISTINCT ( UNION ( StartDates, EndDates ) )

VAR Result = 
    CALCULATE (
        INT ( NOT ( ISEMPTY ( 'Calendar' ) ) ),
        KEEPFILTERS( AllDates ) 
    )

RETURN Result