Performance issue with crossfilter function

[arjunislearning]

Could you please guide how to resolve performance issue with my measure in the below case:
"
Sales Amt =
CALCULATE(
SUM(sales_table[sales_amt]),
CROSSFILTER(slicer_table_sales_at_prod[cust_id], bridge_slicer_tables[cust_id], Both),
CROSSFILTER(slicer_table_spend_at_prod[cust_id], bridge_slicer_tables[cust_id], Both)
)
"
In the sample file(which is just a subset of larger model but exact same relationship nature of the table used), I have use-case of this report to work like:
1. From the first set of slicer (1. Customers that belong to), user can select a condition that will determine dimension values in the report. Say, select those users that belong to a particular Geo and having non-zero SALES for selected products in a particular quarter [and having non-zero SPEND for selected products in a particular quarter].
2. Now KPI slicers will further slice the to show KPI (SALES) values for the above selected customers for a paricular Quarter [made from selected channel]

Please note that one of the use-case should return values in the table like return SALES values for Q1-24(20241) for those customers that have non-zero sales in Q4-23 & non-zero SPEND in Q1-24.
But in another case I might ask to return SALES values for Q1-24(20241) for those customers that have non-zero sales in Q4-23 & no condition for SPEND.
My alternate approach using SUMMARIZE fails for the later use-case where I don't want to involve table 'slicer_table_spend_at_prod'. I understand that crossfilter only engage the table when a values is selected in the 'Cust with SPEND>0' slicers but SUMMARIZE function is always creating a temp table on 'slicer_table_spend_at_prod' to filter the fact records.

Reasoning behind the alternate solution to crossfilter approach: in the final form of our report we have 6 tables of type slicer_table_* nature and the visual is getting very slow. (specially when when we don't engage all the tables used in crossfilter means applying less slicers which is normal when user don't use any slicer at all)
But this is the case where we start getting wrong values in the alternate approach using SUMMARIZE (because summarize is filtering by temp tables irrespective of slicers used or not)
Could you please suggest a solution here

File: https://drive.google.com/drive/folders/1JBrvkUlmloEHVSIs3HUqtEHgAPA9ysnX?usp=sharing

Regards,

Arjun

@Greg_Deckler @mikeh @Mikelytics 

[lbendlin]

Any particular reason for not using disconnected slicers and TREATAS?

[arjunislearning]

Thanks @lbendlin for taking out time and sharing your thought!

Yes TREATAS can also be used as an alternate to SUMMARIZE but my challenge is mandatory consideration of filter table created by TREATAS/SUMMARIZE.

As I shared a limitation originally: "My alternate approach using SUMMARIZE fails for the later use-case where I don't want to involve table 'slicer_table_spend_at_prod'. I understand that crossfilter only engage the table when a values is selected in the 'Cust with SPEND>0' slicers but SUMMARIZE function is always creating a temp table on 'slicer_table_spend_at_prod' to filter the fact records."

I want to engage the filter table only when a slicer is used, like below:

"

Sales Amt(Conditional Approach) =
var sales_slicer_used = ISFILTERED(slicer_table_sales_at_prod)
var spend_slicer_used = ISFILTERED(slicer_table_spend_at_prod)

return IF(
sales_slicer_used && spend_slicer_used,
--When Slicers on both tables is applied
CALCULATE(
SUM(sales_table[sales_amt]),
SUMMARIZE(slicer_table_sales_at_prod, bridge_slicer_tables[cust_id]),
SUMMARIZE(slicer_table_spend_at_prod, bridge_slicer_tables[cust_id])
),
IF( sales_slicer_used ,
--When Slicer on only SALES tables is applied
CALCULATE(
SUM(sales_table[sales_amt]),
SUMMARIZE(slicer_table_sales_at_prod, bridge_slicer_tables[cust_id])
),
IF( spend_slicer_used ,
--When Slicer on only SPEND tables is applied
CALCULATE(
SUM(sales_table[sales_amt]),
SUMMARIZE(slicer_table_spend_at_prod, bridge_slicer_tables[cust_id])
),
--When none of the Slicers are applied
CALCULATE(
SUM(sales_table[sales_amt])
))))

"

This code works but it grows when I have 6 slicer tables to consider all the combination of slicer tables enagagement. 

That was the reason I liked using CROSSFILTER as it engaged tables only when Slicers are used but it poses performance challenges

[lbendlin]

I think you will want to refactor your requirements. They are a bit too ambitious for what Power BI can do.

[arjunislearning]

Apologies @lbendlin if my language is not clear!

Could you please suggest which part of mesage can be detailed /re-phrased to clear ambiguity

[lbendlin]

Nothing wrong with your language. Power BI is not a network graph based BI tool. Unlike Qlik, for example.