Measure in PBI with CALCULATE and FILTER

3
votes

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.

enter image description here

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?

Thanks!

2
powerbidaxmeasure

2 Answers

1
votes

I'd recommend creating a calculated column which evaluates your condition for each row only once.

SatisfiesConditions =
IF (
    'OPERACIONES'[id_type_sale] = 4
        || 'OPERACIONES'[id_type_sale] = 5
        || 'OPERACIONES'[line_sin_origen] = 1
        || 'OPERACIONES'[line_fuera_plazo] = 1
        || 'OPERACIONES'[line_manual] = 1,
    1,
    0
)

Then your measure can be simplified to

Nº operations with conditions =
CALCULATE (
    'OPERACIONES'[Nº lines];
    'OPERACIONES'[SatisfiesConditions] = 1
)
1
votes

Try this:

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

In a few situations, I've noticed a significant increase in performance by just removing the FILTER function and filtering implicitly.

Another option would be something like this (not tested):

Nº operations with conditions :=
CALCULATE (
    [Nº lines],
    KEEPFILTERS (
        FILTER (
            SUMMARIZE (
                'OPERACIONES',
                '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
        )
    )
)