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/counti...
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.

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

    • JonPBNES's avatar
      JonPBNES
      Helper I
      7 years ago

      jdbuchanan71 

       

      Oh, that's good. I'm falling down, possibly because of having to nest the USERELATIONSHIP functions within the calculation to replicate "open" (so, calling the two date fields). 

       

      I've got

       

       

      OpenFilter = if(CALCULATE(
    COUNT(my table[Client_ID]),
    USERELATIONSHIP(ActiveCasesDateTable[Date],my table[start_date]),
	FILTER(my table, my table[start_date]<=LASTDATE(ActiveCasesDateTable[Date])) &&
    (USERELATIONSHIP(my table[start_date],ActiveCasesDateTable[Date]) &&
              FILTER(my table,my table[end_date]>=LASTDATE(ActiveCasesDateTable[Date])
)))>=1,"In",Out")

      Which is throwing up that annoyingly vague "function filter has been used in a TRUE/FALSE expression that is used in a table filter expression, this is not allowed" error. 

       

       

      Any further thoughts hugely appreciated!

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        I think you only need to call the use relationship once in the calculate and you can pull the compare value into a VAR to convert it to a scalar.  Give this a try:

        CP OpenFilter =
VAR CompareDate =
    LASTDATE ( ActiveCasesDateTable[Date] )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'my table' ),
            FILTER (
                ALL ( 'my table'[start_date], 'my table'[end_date] ),
                'my table'[start_date] <= CompareDate
                    && 'my table'[end_date] >= CompareDate
            ),
            USERELATIONSHIP ( ActiveCasesDateTable[Date], 'my table'[start_date] )
        ) >= 1,
        "In",
        "Out"
    )

         If this doesn't work, can you share your model for further testing?