Forum Discussion

markpatton's avatar
markpatton
Helper I
4 years ago

Using FILTER with CROSSFILTER and USERELATIONSHIP

Is there a way to use FILTER with inactive relationships?

 

I have the following formula and it does not work.

 

Private Flying - Trips - Owner Other = calculate(sum('GL - GENERAL LEDGER DETAILS'[Line Amount]), crossfilter('RLS-Plane ID'[registration],'Trip filters'[Plane], none), USERELATIONSHIP('RLS-Plane ID'[registration], 'GL - Customers'[CustomerPlane]), 'GL - Customers'[Customer Type] = "Owner", 'GL - Account'[Account Number] = "11000", filter('GL - GENERAL LEDGER DETAILS', not(RELATED('GL - Customers'[CustomerPlane]) = related('Trip filters'[Plane])))) * -1
 
When I change the relationships in the model (make the relationship in crossfilter inactive and make the relationship in userelationship active) it works fine. When I do this the filter function works as expected. But when they are active and intactive respectively, the filter function does not work. 
 
Im expecting the crossfilter to and userelationship to do the same thing as switching in the model mannually but for some reason when I use them, FILTER does not work but when the model is modified to do the same it works. I have verified this by using crossfilter and use relationship and viewing the filtered records. It does what I expect showing the records I would expect before the FILTER function. When I add FILTER to it, then all records are filtered.
 
Any ideas? Is there a way to do this?
Need Help

7 Replies

    • markpatton's avatar
      markpatton
      Helper I
      4 years ago

      I'd love to but the data is confidential. I'll try to produce a test file. edhans I tried the formula and got the error "The expression refers to multipe columns. Multiple columns cannot be converted to a scaler value"

      • edhans's avatar
        edhans
        Community Champion
        4 years ago

        Sorry - ISBLANK needs a scalar value, not a table. This will fix that. 

        Owner Other =
CALCULATE(
    SUM( 'GL - GENERAL LEDGER DETAILS'[Line Amount] ),
    CROSSFILTER ( 'RLS-Plane ID'[registration], 'Trip filters'[Plane], NONE ),
    USERELATIONSHIP ( 'RLS-Plane ID'[registration], 'GL - Customers'[CustomerPlane] ),
    'GL - Customers'[Customer Type] = "Owner",
    'GL - Account'[Account Number] = "11000",
    ISBLANK(
        COUNTROWS(
            CALCULATETABLE(
                'GL - GENERAL LEDGER DETAILS',
                TREATAS(
                    VALUES( 'GL - Customers'[CustomerPlane] ),
                    'Trip filters'[Plane]
                )
            )
        )
    )
) * -1
  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    Hard to know without data, but try this:

    Owner Other =
CALCULATE(
    SUM( 'GL - GENERAL LEDGER DETAILS'[Line Amount] ),
    CROSSFILTER ( 'RLS-Plane ID'[registration], 'Trip filters'[Plane], NONE ),
    USERELATIONSHIP ( 'RLS-Plane ID'[registration], 'GL - Customers'[CustomerPlane] ),
    'GL - Customers'[Customer Type] = "Owner",
    'GL - Account'[Account Number] = "11000",
    ISBLANK(
        CALCULATETABLE(
            'GL - GENERAL LEDGER DETAILS',
            TREATAS(
                VALUES( 'GL - Customers'[CustomerPlane] ),
                'Trip filters'[Plane]
            )
        )
    )
) * -1


    TREATAS doesn't use the model for relationships, so I've created a virtual relationship, applied it to the GL table, and am expecting a blank - i.e. no records returned. If records are returned, it is because customerplane matches (filters) Plane.

  • markpatton's avatar
    markpatton
    Helper I
    4 years ago

    edhans  with that change I now get "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

     

    I'm working on a test pbix. Hopefully I get the same results with test data.