locked
Measure in PBI with CALCULATE and FILTER RRS feed

  • Question

  • Hi

    I have one report with 3 visual filters (date, country and type) and a table that list the stores and number of operatios with the aplied filters. I added a column with the number of operations that meet a condition, but this measure is very slow.



    Here the measure:

    Nº operations with conditions = CALCULATE('OPERACIONES'[Nº lines];
    FILTER('OPERACIONES';'OPERACIONES'[id_type_sale] = 4 ||
    'OPERACIONES'[id_type_sale] = 5 ||
    ( 'OPERACIONES'[line_sin_origen]=1 
    || 'OPERACIONES'[line_fuera_plazo]=1 
    || 'OPERACIONES'[line_manual]=1)

    ))

    I think that it is slow because the FILTER function is filtering all data in the table (this table has billions of records)
    How can I improve this measure?

    I am using direct query to a SSAS-T and I can't add calculated columns in Power BI.
    I could add a calculated column in the model SSAS-T with the measure, but I am searching other solutions before alter my model.

    Thanks!

    Thursday, February 14, 2019 8:28 AM

Answers

  • One option is this:

    Nº operations with conditions =
    CALCULATE (
        'OPERACIONES'[Nº lines];
        KEEPFILTERS (
            FILTER (
                ALL (
                    'OPERACIONES'[id_type_sale];
                    'OPERACIONES'[line_sin_origen];
                    'OPERACIONES'[line_fuera_plazo];
                    'OPERACIONES'[line_manual]
                );
                'OPERACIONES'[id_type_sale] = 4
                    || 'OPERACIONES'[id_type_sale] = 5
                    || ( 'OPERACIONES'[line_sin_origen] = 1
                    || 'OPERACIONES'[line_fuera_plazo] = 1
                    || 'OPERACIONES'[line_manual] = 1 )
            )
        )
    )
    

    If you have billions of rows and these columns have a low cardinality, this could be better:

    Nº operations with conditions =
    CALCULATE (
        'OPERACIONES'[Nº lines];
        KEEPFILTERS (
            FILTER (
                CROSSJOIN (
                    CROSSJOIN (
                        ALL ( 'OPERACIONES'[id_type_sale] );
                        ALL ( 'OPERACIONES'[line_sin_origen] )
                    );
                    CROSSJOIN (
                        ALL ( 'OPERACIONES'[line_fuera_plazo] );
                        ALL ( 'OPERACIONES'[line_manual] )
                    )
                );
                'OPERACIONES'[id_type_sale] = 4
                    || 'OPERACIONES'[id_type_sale] = 5
                    || ( 'OPERACIONES'[line_sin_origen] = 1
                    || 'OPERACIONES'[line_fuera_plazo] = 1
                    || 'OPERACIONES'[line_manual] = 1 )
            )
        )
    )
    

    I suggest you reading this article:

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/



    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    • Proposed as answer by Darren GosbellMVP Thursday, February 14, 2019 10:22 PM
    • Marked as answer by CristinaJ Friday, February 15, 2019 8:13 AM
    Thursday, February 14, 2019 5:46 PM

All replies

  • One option is this:

    Nº operations with conditions =
    CALCULATE (
        'OPERACIONES'[Nº lines];
        KEEPFILTERS (
            FILTER (
                ALL (
                    'OPERACIONES'[id_type_sale];
                    'OPERACIONES'[line_sin_origen];
                    'OPERACIONES'[line_fuera_plazo];
                    'OPERACIONES'[line_manual]
                );
                'OPERACIONES'[id_type_sale] = 4
                    || 'OPERACIONES'[id_type_sale] = 5
                    || ( 'OPERACIONES'[line_sin_origen] = 1
                    || 'OPERACIONES'[line_fuera_plazo] = 1
                    || 'OPERACIONES'[line_manual] = 1 )
            )
        )
    )
    

    If you have billions of rows and these columns have a low cardinality, this could be better:

    Nº operations with conditions =
    CALCULATE (
        'OPERACIONES'[Nº lines];
        KEEPFILTERS (
            FILTER (
                CROSSJOIN (
                    CROSSJOIN (
                        ALL ( 'OPERACIONES'[id_type_sale] );
                        ALL ( 'OPERACIONES'[line_sin_origen] )
                    );
                    CROSSJOIN (
                        ALL ( 'OPERACIONES'[line_fuera_plazo] );
                        ALL ( 'OPERACIONES'[line_manual] )
                    )
                );
                'OPERACIONES'[id_type_sale] = 4
                    || 'OPERACIONES'[id_type_sale] = 5
                    || ( 'OPERACIONES'[line_sin_origen] = 1
                    || 'OPERACIONES'[line_fuera_plazo] = 1
                    || 'OPERACIONES'[line_manual] = 1 )
            )
        )
    )
    

    I suggest you reading this article:

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/



    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    • Proposed as answer by Darren GosbellMVP Thursday, February 14, 2019 10:22 PM
    • Marked as answer by CristinaJ Friday, February 15, 2019 8:13 AM
    Thursday, February 14, 2019 5:46 PM
  • Good morning

    Thanks a lot! Applied change has improved performance (1min 45sec to 41sec).

    And thanks for the recommended reading.

    Friday, February 15, 2019 8:13 AM