Forum Discussion

JSher's avatar
JSher
Frequent Visitor
5 years ago
Solved

Filter a datasource(tableview) using date/time column off of a date, begintime and endtime

I have a drill through where I am on a page with a date,begintime and endtime measures in Table1.

 

I wish to show a filtered table view of Table2.  Table 2 has a date/time column.

 

How do I filter the table view of Table2 using measures from Table1.

 

I was looking at creating a filtered table but cannot figure out how to do times, only dates such as this:

FilteredTable = 
CALCULATETABLE(
    Table2,
    DATESBETWEEN ( Table2[Date], Table1[BeginDate], Table1[EndDate] )
)

Thanks

 

 

General Comment
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi JSher ,

    Since you have a drillthrough page, you can create a measure like this, put it in the table visual filter of table2 in the target page and set its value as 1:

    A =
VAR tab =
    ADDCOLUMNS (
        'Table1',
        "BD",
            CONVERT ( [date] & " " & [BeginDate], DATETIME ),
        "ED",
            CONVERT ( [date] & " " & [EndDate], DATETIME )
    )
VAR bdt =
    MAXX ( tab, [BD] )
VAR edt =
    MAXX ( tab, [ED] )
RETURN
    IF (
        SELECTEDVALUE ( Table2[Date] ) >= bdt
            && SELECTEDVALUE ( Table2[Date] ) <= edt,
        1,
        0
    )

     

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

3 Replies

    • JSher's avatar
      JSher
      Frequent Visitor
      5 years ago

      This is what I am looking for but with Time.  Any idea how to incorperate 2 time variables and 1 date variable instead of 2 date variables?

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi JSher ,

    Since you have a drillthrough page, you can create a measure like this, put it in the table visual filter of table2 in the target page and set its value as 1:

    A =
VAR tab =
    ADDCOLUMNS (
        'Table1',
        "BD",
            CONVERT ( [date] & " " & [BeginDate], DATETIME ),
        "ED",
            CONVERT ( [date] & " " & [EndDate], DATETIME )
    )
VAR bdt =
    MAXX ( tab, [BD] )
VAR edt =
    MAXX ( tab, [ED] )
RETURN
    IF (
        SELECTEDVALUE ( Table2[Date] ) >= bdt
            && SELECTEDVALUE ( Table2[Date] ) <= edt,
        1,
        0
    )

     

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