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...
  • 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. 

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.