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.

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!