Forum Discussion
Using FILTER with CROSSFILTER and USERELATIONSHIP
markpatton - can you please share an example pbix file?
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"
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] ) ) ) ) ) * -1I 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.
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
