Measure CALCULATE(SUMX..... with IN works and NOT IN (<>) doens´t work... please help.

[BMenescal]

Hello everybody!

I´m new on power bi DAX....😥

I'm developing a measure and I didn't understand the reason for the behavior with the use of IN and Not in (<>).

The measure with IN works correctly and with NOT IN does not. The error below is displayed:
"Calculation error in measure (...) A table of multiple values was supplied where a single value was expected."

With IN:
Test Med. com LAST Simulação (Prod Saldo) = CALCULATE(Sumx('Fato Movimentação Fiscal'; 'Fato Movimentação Fiscal'[Valor Bruto (Modelo Agg Last)]); 'Natureza Operação'[Cod. Natureza Operação] = "8.888.01" ; FILTER('Tipo Movimento'; 'Tipo Movimento'[Cod. Tipo Movimento] IN {"2.2.32"; "2.2.17"}))

With NOT IN:

Test Med. com LAST Simulação (Prod Saldo) not in = CALCULATE(Sumx('Fato Movimentação Fiscal'; 'Fato Movimentação Fiscal'[Valor Bruto (Modelo Agg Last)]); 'Natureza Operação'[Cod. Natureza Operação] = "8.888.01" ; FILTER('Tipo Movimento'; 'Tipo Movimento'[Cod. Tipo Movimento] <> {"2.2.32"; "2.2.17"}))

 

Does anyone know the reason for this behavior? what am I doing wrong?

Thanks in advance.

Bru.

[Gordonlilj · Accepted solution]

Hi,

Try something like

measure = CALCULATE(
    Sumx('Fato Movimentação Fiscal';
     'Fato Movimentação Fiscal'[Valor Bruto (Modelo Agg Last)]);
      'Natureza Operação'[Cod. Natureza Operação] = "8.888.01" ;
       FILTER('Tipo Movimento';
        NOT('Tipo Movimento'[Cod. Tipo Movimento]) IN {"2.2.32"; "2.2.17"}))

[BMenescal]

Hi @Gordonlilj , thanks for reply.

I be able to display the values in the view without errors, but the values still wrong.... very strange. 

Look:
I don't understand ... but the values are very close to the correct.

[v-chuncz-msft]

@BMenescal 

You may check if it is affected by some blank value.

[BMenescal]

I changed the measure by adding a filter condition at the end of the formula to get the last day of the month:

Test Med. com LAST Simulação (Prod Saldo) not in = CALCULATE(Sumx('Fato Movimentação Fiscal'; 'Fato Movimentação Fiscal'[Valor Bruto (Modelo Agg Last)]); 'Natureza Operação'[Cod. Natureza Operação] = "8.888.01" ; FILTER('Tipo Movimento'; NOT 'Tipo Movimento'[Cod. Tipo Movimento] IN {"2.2.32"; "2.2.17"}) ; FILTER('Tempo'; DAY('Tempo'[Data Final]) = 'Tempo'[Último Dia Mês]))

Now it works!

[BMenescal]

@v-chuncz-msft thanks for the reply! I opened the values ​​in a specific month to check if they are ok ... The values ​​match but the result of the month is not correct because it should display the last value ....

look: