Table result based on between dates

[IF]

Hi,

I want to see the result as a table based on the selections from the slicers Table1[M_ID] and Table1[V_ID]. The result should be filtered based on Table1[A_Date] by checking in Table2. If the date of Table1[A_Date] is between the range of Table2[Start_Date] and Table2[End_Date], it should be listed in the table.

Table1

A_Date	M_ID	V_ID
7/17/2020	M23	V3435
7/17/2019	M34	V3422
6/17/2020	M22	V3222

Table2

Start_Date	End_Date	M_ID.1	S_ID
7/1/2020	8/1/2020	M23	S1435
7/17/2018	8/1/2020	M23	S1435
5/17/2019	5/10/2020	M22	S1222
5/1/2018	5/10/2020	M22	S1262
1/1/2018	7/10/2020	M22	S1312
5/1/2018	5/10/2019	M34	S1262

For example; if Table1[V_ID] is V3422, it shouldn't bring any result. Or if Table1[V_ID] is V3222, it should bring the row for S1222 and S1312.

Thanks in advance!

[v-xiaotang · Accepted solution]

Hi @IF 

try this,

create the measure, and put it into the visual level filter

filterMeasure = 
    var _selectMID=SELECTEDVALUE(Table1[M_ID])
    var _selectDate=SELECTEDVALUE(Table1[A_Date])
return 
    IF(MIN(Table2[Start_Date])<=_selectDate && MIN(Table2[End_Date])>=_selectDate && MIN(Table2[M_ID.1])=_selectMID,1,0)

result

Best Regards,

Community Support Team _Tang

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

[smpa01]

@IF  please try this as a measure

Measure =
VAR _0 =
    MAX ( 'Table 1'[A_Date] )
VAR _1 =
    CALCULATE (
        MAX ( 'Table 2'[M_ID.1] ),
        FILTER ( 'Table 2', _0 <= 'Table 2'[Start_Date] && 'Table 2'[End_Date] >= _0 )
    )
RETURN
    _1