Using FILTER with CROSSFILTER and USERELATIONSHIP

[markpatton]

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?

[markpatton]

@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.

[edhans]

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.

[Daryl-Lynch-Bzy]

@markpatton  - can you please share an example pbix file?

[markpatton]

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]

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

[markpatton]

I have reproduced in the simpleset form. File is available at 

dropbox: https://www.dropbox.com/s/3hqay3lnrpw1o5y/filter%20test.pbix?dl=0

google: https://drive.google.com/file/d/1PUWzenUSDEnMf0z7etpenJOJxzgKLSOi/view?usp=sharing

There are two measures, one with FILTER and one without. If you change the active relationships in the model (make Planes-Customer active, Planes-Trips) inactive. You can see how I want it to work. Only trips where Plane != Customer Plane should be shown. When the relationships are in this state the measure works fine. When you switch the acitive state (Planes-trips active, planes-customers inactive) then the filter measure does not work. 

For my model I need to have the Planes-Trips as the active relationship and this is why the measure is needed.

[v-shex-msft]

Hi @markpatton,

I do not recommend you to do changes to the interactions of visuals, they will ignore the filter effects and some DAX functions don't work. (visual and Dax expression filter effects are based on interactions and relationships)

Change how visuals interact in a report - Power BI | Microsoft Docs

Understand how visuals interact in a report - Power BI | Microsoft Docs
Regards,

Xiaoxin Sheng