Forum Discussion

AleMacedo's avatar
AleMacedo
Icon for Helper I rankHelper I
4 years ago
Solved

Allexecpt with multiple filters

Hi all,

I have a single table of monthly  produtivity employees reports.

I have a measure for media for each task category and need to calculate media for all company in each category with filters options to year, task category, Unit Group and employee group. 

My fact table ("Rels") has : Initial Date , Final Date (in a single month per line) ,  Unit Id, AuthorId, Task Category, Value (number of tasks done), and employee group.

 

I have others table to calendar, Units Name with column to unit group,  Tasks, Employee Group and Employees with Id

 

I have a measure to media = average (Rels[Valoue])

My Media - all Company = AVERAGEX (ALLEXCEPT(Rels, Rels[Category],Calendar[Date],Rels[EmployeeGroup],Units[UnitGroup]),Mesuare[Media])

 

This Media - all company is not working.

What I need to change?

 

My schema:

 

 

Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi AleMacedo 

    Try code like below,

    Media MPSP =
    calculate(
    [Media],
    Filter(ALLEXCEPT(Rels,Atributos[Atributo],Relatorios[Relatorio],Calendario[Date].[Date],UnidadeAdministrativa[Entrancia]),Rels[date]= min(Calendario[Date])  ))
    Generally speaking, it filters the table Rels based on the date in the current row.
     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago
    =
CALCULATE (
    [Media],
    ALLEXCEPT (
        Rels,
        Rels[Category],
        Calendar[Date],
        Rels[EmployeeGroup],
        Units[UnitGroup]
    )
)
    • AleMacedo's avatar
      AleMacedo
      Icon for Helper I rankHelper I
      4 years ago

      I did it:

      Media MPSP =
      calculate(
      [Media],
      ALLEXCEPT(Rels,Atributos[Atributo],Relatorios[Relatorio],Calendario[Date].[Date],UnidadeAdministrativa[Entrancia]))
       
      but its not considering filter for date. All the other filters are ok.
      • v-xiaotang's avatar
        v-xiaotang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi AleMacedo 

        Try code like below,

        Media MPSP =
        calculate(
        [Media],
        Filter(ALLEXCEPT(Rels,Atributos[Atributo],Relatorios[Relatorio],Calendario[Date].[Date],UnidadeAdministrativa[Entrancia]),Rels[date]= min(Calendario[Date])  ))
        Generally speaking, it filters the table Rels based on the date in the current row.
         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.