Filtering in DAX Query for Paginated report, can I make my filter more efficient?

[Anonymous]

Hi 

I have a DAX virtual table that is a cross join between 2 tables in the model, which I need to filter in a paginated reports Data Sets. the Tabular Model needs to be filtered on 2 lookup tables, each of which has a foreign key in the table, in this I have called them code 1 and code 2, Table 1 is the DAX virtual table, here is the filter statement:-

var __DS0FilterTable1 =
FILTER (
'Table 1',
IF (
_Default = "Yes",
'Table 1'[Default Access]="Yes",
PATHCONTAINS ( _Input, 'Table 1'[Code 1]) || PATHCONTAINS ( _Input, 'Table 1'[Code 2])
)
)

Obviously it is a filter used in the final query statement, because the table is very large, caused by the cross join, the filtering is quite slow, I noted in some other posts that the advice is not to use the filter function, but I can't see how to make this filter with out using the filter function

Any help much appreciated

Andy

[colacan]

@Anonymous  Hi AndyMay,

It may help if you filter the Table1 when you make it.

Table1 = 
    CALCULATETABLE(
        CROSSJOIN( Cross1, Cross2 ),
        cross1[Default Access]="Yes")

if you need to filter it based on a column value from Cross2, you can wrap it again with CALCULATETABLE(... , Cross2[col] = "")

Hope this helps you.

Thanks.

Please mark it as solution if this helped you.

[Anonymous]

Hi colacan

I have filtered it by applying row level security, I can't filter on Default Access = "Yes" because I need the "no" for the else part of the filter

Thankyou very much for your thoughts  

Regards

Andy

[AlexisOlson · Accepted solution]

I'm not sure tinkering with just this specific part will be able to help greatly. Maybe you don't need such a large cross join to begin with? In any case, it would be useful to see more context (e.g. the whole query and the cross join table definition).