Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |