Forum Discussion

JonPBNES's avatar
JonPBNES
Helper I
7 years ago
Solved

Apply visual level interactions without a relationship

I know, it sounds silly right? 

 

So, we've got a start date, and end date and a client ID. We wanted an open cases measure. We followed the terribly useful https://powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbell/ guidance, which works a treat. 

 

One snag is the fact that it relies on no relationships between the date table and the source table means that any other metrics (count of starts, say) can't be controlled from the same filter. This has historically been a minor annoyance, but fixable within the design. 

 

The problem now comes in that we've been asked to provide an associated Client ID list to go alongside the open clients list (so list of clients between active dates).

 

I'm wondering if there's a fix for this that allows for visual-level filtering from the graph itself (rather than a table with yet another group of slicers). 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Good morning JonPBNES 

    As is typical, turns out we were over thinking it.  We didn't need USERELATIONSHIP, we can just read the date range selected, feed that to our filter measure and go.  We do still need to compare Start Date and End Date to the date range.

    OpenCaseFilter = 
VAR CompareStartDate = FIRSTDATE ( OpenCasesTable[Date] )
VAR CompareEndDate = LASTDATE ( OpenCasesTable[Date] )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'openfiltertest' ),
            FILTER (
                 openfiltertest,
                'openfiltertest'[Start Date] <= CompareEndDate
                    && 'openfiltertest'[End Date] >= CompareStartDate
            )
        ) >= 1,
        "In",
        "Out"
    )

    You can see the result we want even after I deleted the link between the two tables.

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    Hi JonPBNES,

    Have you tried to make an inactive relationship between both tables and then make a calculated measure with USER ELATION SHIP to activate the relationship?

    That you you can have the inactive relationship but also an active one that is call when needed.

    Regards,
    MFelix
    • JonPBNES's avatar
      JonPBNES
      Helper I
      7 years ago

      The inactive relationships certainly don't nerf the calculation and will be useful for some other things we're trying to develop; but I'm struggling to think of how to apply them to allow a visual interaction. 

       

       

       

       

       

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        If you were trying to filter table 1 based on the rows in table 2 you could do something like.

        FilterFromT2 =
IF (
    ISBLANK (
        CALCULATE ( COUNTROWS ( Table2 ), USERELATIONSHIP ( Table1[key], Table2[key] ) )
    ),
    BLANK (),
    1
)

        Then you apply that as a visual level filter and set it to FilterFromT2 = 1