Forum Discussion

IF's avatar
IF
Post Prodigy
4 years ago
Solved

Table result based on between dates

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!

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

2 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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

     

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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.