Forum Discussion

PaulBI's avatar
PaulBI
Frequent Visitor
7 years ago
Solved

Drillthrough Between Dates

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 potential...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
PaulBI
Frequent Visitor
7 years ago

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's avatar
    PaulBI
    Frequent Visitor
    7 years ago

    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],
        ","
    )