Forum Discussion
Performance issue with crossfilter function
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
I think you will want to refactor your requirements. They are a bit too ambitious for what Power BI can do.
Apologies lbendlin if my language is not clear!
Could you please suggest which part of mesage can be detailed /re-phrased to clear ambiguity
Nothing wrong with your language. Power BI is not a network graph based BI tool. Unlike Qlik, for example.