Allexecpt with multiple filters

[AleMacedo]

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:

[CNENFRNL]

=
CALCULATE (
    [Media],
    ALLEXCEPT (
        Rels,
        Rels[Category],
        Calendar[Date],
        Rels[EmployeeGroup],
        Units[UnitGroup]
    )
)

[AleMacedo]

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 · Accepted solution]

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.

[AleMacedo]

Thank you!!