Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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).
@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.
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
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).
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |