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.

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?

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi JonPBNES ,

       

      If I understand your need correctly, you don't need to have a list based on the active start and end date, correct, taking into account that you have no relationship between the two tables.

       

      I know I suggested to use the USERELATIONSHIP but looking at your code and reading once again your initial post believes that the best way is to make a measure as jdbuchanan71  suggested and use it on the filter visuals that way when you use your slicer the other visual will be also updated.

       

      Can you share sample data?

       

      Regards,

      MFelix

      • JonPBNES's avatar
        JonPBNES
        Helper I
        7 years ago

        MFelix jdbuchanan71 

         

        I had a go at that measure and got a part of the way there. It is returning cases that both started and finished within the period, as opposed to those that are open in the quarter (ie has a start date before period) and I can't quite work out why.

         

        Intuitively I feel it needs to use both relationships somehow, but then I start getting lost. 

         

        I have simplified a test data model, recreated working to date. This is here 

         

        Thanks again both for your time!