Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV
Helper IV

DAX Optimizer - Filtered table as iterator



I just tried out the new free trial version of DAX optimizer tool and it flagged the issue with one of my measures. I was suspecting that it may be the one cuasing the performance issues in one of my reports so it's nice that the tool confirmed it. It flags the issue as "Filtered table as iterator" so I understand that putting a filtered table in the sumx is causing problems. Any idea how I could change the measure below to be less heavy on performance?



Final POS Total Calculated Cost Trx =
    FILTER (
        //sums all the distributors that doesn't have detailed POS and ensuring that detailed level will result in zero
        IF (
            HASONEFILTER ( dim_product[Product Family Code] )
                || HASONEFILTER ( dim_product[BU (+)] )
                || HASONEFILTER ( dim_product[Model Group Code] )                
                || HASONEFILTER ( dim_product[Market Model Code] )              
                || HASONEFILTER ( dim_product[Model] )
                || HASONEFILTER ( dim_product[Item No (f)] ),
            BLANK (),
            COUNTROWS (
                FILTER (
                    AND(fct_detailed_pos[Provider No] = fct_high_level_pos_inv[Provider No],
                        fct_detailed_pos[POS Month (+)] = fct_high_level_pos_inv[POS Month (+)])
            ) = 0
    fct_high_level_pos_inv[HL POS Cost Trx]*RELATED(dim_distributor[Net Revenue Factor POS (+)])
)+ [Det POS_Total Calculated Cost Trx]  // then adds all the detailed POS
What the measure doing is summing all the high level POS (from fct_high_level_pos_inv table) for those distributors that didn't report detailed POS (in fct_detailed_pos table) and ensuring that when filtering on detailed level (HASONEFILTER (dim_product[Product Family Code]|| HASONEFILTER ( dim_product[BU (+)] ) etc.) the measure will result in zero instead of showing a detailed number. 
Super User
Super User

Their documentation/knowledge base shows potential workarounds. As always you want to use DAX Studio to validate that the change results in an actual improvement.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors