Forum Discussion

Aucesar's avatar
Aucesar
Helper III
3 years ago
Solved

Optimize DAX Filter

Hi Community,

 

How to make this in a better format, it´s working but repeating every <> makes no sense for me as I´m using the same table/column

 

Also I need to convert from WholeNumber to Text how I can acomplish it using Wholenumber?

 

Thanks.

SomaDespesasValorNominal =
CALCULATE(
SUM('BI-Financeiro-Despesas'[ValorNominal]),
USERELATIONSHIP('BI-Financeiro-Despesas'[DataEmissao], Calendario[Date]),
FILTER (
'BI-Financeiro-Despesas',
('BI-Financeiro-Despesas'[Documento] <> "CH PRE DATADO" && 'BI-Financeiro-Despesas'[CodBanco] <> "80018" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "76" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "98" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "107" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "991" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "992" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "993" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "994" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "995" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "996" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "997" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "998" && 'BI-Financeiro-Despesas'[CodClassePagamento] <> "999"
)))

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    If there is no misunderstanding, this shall work:

     

    SomaDespesasValorNominal =
    CALCULATE(
        SUM('BI-Financeiro-Despesas'[ValorNominal]),
        USERELATIONSHIP('BI-Financeiro-Despesas'[DataEmissao], Calendario[Date]),
        FILTER (
            'BI-Financeiro-Despesas',
            ('BI-Financeiro-Despesas'[Documento] <> "CH PRE DATADO" 
            && NOT 'BI-Financeiro-Despesas'[CodBanco] IN { "80018", "76", "98", "107", "991", "995", "996", "997", "998", "999"}
            )
        )
    )

     

    You can also maintain a table for the values you want to filter out. It would be even better. 

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    If there is no misunderstanding, this shall work:

     

    SomaDespesasValorNominal =
    CALCULATE(
        SUM('BI-Financeiro-Despesas'[ValorNominal]),
        USERELATIONSHIP('BI-Financeiro-Despesas'[DataEmissao], Calendario[Date]),
        FILTER (
            'BI-Financeiro-Despesas',
            ('BI-Financeiro-Despesas'[Documento] <> "CH PRE DATADO" 
            && NOT 'BI-Financeiro-Despesas'[CodBanco] IN { "80018", "76", "98", "107", "991", "995", "996", "997", "998", "999"}
            )
        )
    )

     

    You can also maintain a table for the values you want to filter out. It would be even better. 

    • Aucesar's avatar
      Aucesar
      Helper III
      3 years ago

      Hi Thanks,

      The suggestion to create a table with filters is good, and using NOT and IN is the solution.