Performance issue with crossfilter function
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 MikelyticsDAX Optimization SUMX ( SUMMARIZE ) - Performance Issue
I'm not able to optimize this measure. I'm new to Power BI DAX. Kindly suggest me a way to run this DAX faster. If I am wrong, please suggest me any other alternative way to achieve the below DAX DAX Measure: Measure name = SUMX ( SUMMARIZE (table_name', table_name'[Col1],table_name'[Col2],table_name'[col3],table_name'[col4],table_name'[col5], "result", CALCULATE ( DISTINCTCOUNTNOBLANK ( table_name'[col1] ), FILTER ( table_name', SUM ( table_name'[Counter] ) = 1 ) ) ), [result] )
