Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

3 REPLIES 3
colacan
Resolver II
Resolver II

@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
Not applicable

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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors