CODEWITHSUNDEEP
filterpowerbimeasure
Rafael Cunha
Rafael Cunha

Reputation: 231

Create a measure depending on a filter

I have two measures:

% PARTIC_REC_CORR = 
IF(
    SUM('RECEITA _SERVIÇO'[REC_CORRENTES])=0,
    "_",
    DIVIDE(
        SUM(BD_RH[PESSOAL + ENCARGOS + BENEFÍCIOS]),
        SUM('RECEITA _SERVIÇO'[REC_CORRENTES])
    )
)

% PARTIC_REC_CORR TOTAL = 
IF(
    SUM('REC_CORRENTES_1'[RECEITAS CORRENTES])=0,
    "_",
    DIVIDE(
        SUM(BD_RH[PESSOAL + ENCARGOS + BENEFÍCIOS]),
        SUM('REC_CORRENTES_1'[RECEITAS CORRENTES])
    )
)

Now I want to create a new one depending on the selected filter (dLocal).

dLocal

If nothing is selected, I want % PARTIC_REC_CORR TOTAL, otherwise % PARTIC_REC_CORR. I tried

% PARTIC_REC_CORR 2 = 
IF(
    ALL('dLocal'[SIESTADO]), 
    RH_INDICADORES[% PARTIC_REC_CORR TOTAL], 
    RH_INDICADORES[% PARTIC_REC_CORR]
)

unsuccessfully.

dLocal

SISESTADO CDESTADO
AC        24
AL        02
...       ...

RECEITA _SERVIÇO

DATA_BASE  CDESTADO REC_CORRENTES SISTESTADO
31/12/2018 24       99999,99      AC
31/12/2018 02       99999,99      AL
...        ...      ...           ...
31/12/2019 24       99999,99      AC
31/12/2019 02       99999,99      AL
...        ...      ...           ...

REC_CORRENTES_1

DATA_BASE  REC_CORRENTES
31/12/2018 99999999,99
31/12/2018 99999999,99

Upvotes: 0

Views: 42

Answers (1)

Giovanni Luisotto
Giovanni Luisotto

Reputation: 1400

This should work.

Edit: the measures where in the wrong order

% PARTIC_REC_CORR 2 = 
IF(
    ISFILTERED('dLocal'[SIESTADO])
    ,RH_INDICADORES[% PARTIC_REC_CORR]
    ,RH_INDICADORES[% PARTIC_REC_CORR TOTAL]
)

note that ISFILTERED considers only direct filter on a field.

Your formula is not working because ALL() returns a whole column with all the values (ALL removes the filters), how that is considered inside the IF "check expression" I have no idea.

Upvotes: 1

Related Questions