Dispay Value if any date falls within a filter range

[JRB]

Hi All,

Have a DAX logic problem I cant wrap my head around.

Scenario:

This week = 12/07 - 18/07

I need to filter the below table so that if any date for a Key falls within this week, that all rows, regardless of the date are displayed.

Rows 4, 5, and 9 are filtered out as no dates for those keys fall within the current week.

Rows 3 and 8 are included because another date for the same keys falls within the current week, even though the dates on those rows are outside the range.

FACT TABLE:

Key	Date	Row
1	11-Jul	1
1	17-Jul	2
1	19-Jul	3
2	11-Jul	4
2	25-Jul	5
3	13-Jul	6
3	12-Jul	7
3	30-Jul	8
4	30-Jul	9
5	15-Jul	10
5	16-Jul	11

FILTERED RESULT:

Key	Date	Row
1	11-Jul	1
1	17-Jul	2
1	19-Jul	3
3	13-Jul	6
3	12-Jul	7
3	30-Jul	8
5	15-Jul	10
5	16-Jul	11

I hope this is clear enough.

Thanks in advance,

Jordan

[v-angzheng-msft · Accepted solution]

Hi, @JRB 

First, create a calendar table with a WeekNum column:

Calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

weekNum = WEEKNUM([Date],2)

Slicer: "calendar"[weekNum]

The filter measure will be this:

_Result =
VAR _filter =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Key] = MAX ( 'Table'[Key] ) ),
        [Date]
    )
VAR _isIn =
    CALCULATETABLE (
        ALLSELECTED ( 'Calendar' ),
        TREATAS ( _filter, 'Calendar'[Date] )
    )
VAR _countIsIN =
    COUNTROWS ( _isIn )
VAR _if =
    IF ( COUNTROWS ( _isIn ) <> 0, 1, 0 )
RETURN
    _countIsIN

Finally, filter the items whose results are not empty in the filter pane.

Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@JRB , Assuming slicer is coming from independent table

new measure =
VAR _min = MINX( allselected('Calendar') , 'Calendar'[Date] )
VAR _max = MAXX(allselected('Calendar') , 'Calendar'[Date] )
var _date1 = calculate(min(Table[Date]), filter(allselected(Table), Table[Key] =max(Table[Key])))
var _date2 = calculate(min(Table[Date]), filter(allselected(Table), Table[Key] =max(Table[Key])))
return
calculate(sum(Table[row]), filter(Table, (_date1 >=_min && _date1 <= _max) || (_date2 >=_min && _date2 <= _max)))