Drillthrough Between Dates

[PaulBI]

I have two tables (example below).  I'm trying to setup a drillthrough so that I can display all the comments (from table B) with datetimes between the start and end date in Table A.  There potentially are overlaps in Table A.  I think I'm missing something simple but am struggling to see what I'm missing.  Any help is appreciated.  Thanks!

Table A:

Start	End	ID
6/1/19 0:00	6/1/19 3:00	1
6/2/19 0:00	6/2/19 7:00	2
6/3/19 0:00	6/3/19 0:00	3
6/4/19 0:00	6/4/19 5:00	4

Table B:

Date	Comment
6/2/19 2:00	This is an example
6/4/19 1:00	Example 2

[Anonymous · Accepted solution]

HI @PaulBI ,

Unfortunately, current power bi not supported to use drillthrough filter to pass multiple values between visuals. 

According to your description, it seems like you are try to look up records from table B based on date range defined by current row 'start', 'end' date.

If this is a case, you can try to use following measure formula to look up matched records from table B:

Measure =
VAR _start =
    MAX ( tableA[Start] )
VAR _end =
    MAX ( tableA[End] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( tableB[Comment] ),
            FILTER ( ALL ( tableB ), [Date] IN CALENDAR ( _start, _end ) )
        ),
        [Comment],
        ","
    )

Regards,

Xiaoxin Sheng

[PaulBI]

I had to make a few changes because the time element was being lost in the CALENDAR function, but after that it worked great.  Is there any way to sort that the comments are concated in?  It pulls the right comments now, just they are not always in chronological order.

Thanks for the help so far!

Measure =
VAR _start =
    MAX ( tableA[Start] )
VAR _end =
    MAX ( tableA[End] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( tableB[Comment] ),
            FILTER ( ALL ( tableB ), [Date] >= _start),
            FILTER(ALL(tableB),[Date]<= _end ) )
        ),
        [Comment],
        ","
    )

[PaulBI]

I realized I just needed to use the 4th and 5th positions of the Concatenatex to sort it.  I'm all set.  Thanks for the help!

---

@PaulBI wrote:

I had to make a few changes because the time element was being lost in the CALENDAR function, but after that it worked great.  Is there any way to sort that the comments are concated in?  It pulls the right comments now, just they are not always in chronological order.

Thanks for the help so far!

Measure =
VAR _start =
    MAX ( tableA[Start] )
VAR _end =
    MAX ( tableA[End] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( tableB[Comment] ),
            FILTER ( ALL ( tableB ), [Date] >= _start),
            FILTER(ALL(tableB),[Date]<= _end ) )
        ),
        [Comment],
        ","
    )

---

---

@PaulBI wrote:

I had to make a few changes because the time element was being lost in the CALENDAR function, but after that it worked great.  Is there any way to sort that the comments are concated in?  It pulls the right comments now, just they are not always in chronological order.

Thanks for the help so far!

Measure =
VAR _start =
    MAX ( tableA[Start] )
VAR _end =
    MAX ( tableA[End] )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( tableB[Comment] ),
            FILTER ( ALL ( tableB ), [Date] >= _start),
            FILTER(ALL(tableB),[Date]<= _end ) )
        ),
        [Comment],
        ","
    )

---