Problem with MAXX

[kipi_bi]

Hello Team,

I am trying to get the Maximum Score achieved by any Employee but it is getting filtered at employee level ,even after applying additional filters with ALL and REMOVEFILTER, there is 1 to many relationship frmo Dim_Employee TO Fact_employee_training

Fact Table: 

• EmployeeID,ProgramID,SkillID,CertificationID,CompletionDate,Score

  Dim_Table
  
  EmployeeID,FullName,Department,Location,JoiningDate,SkillName

Regards,

Ritz.

[Ahmedx · Accepted solution]

pls try

MaxTotalScorePerEmployee =
CALCULATE(
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                ALL(Dim_Employee),
                Dim_Employee[EmployeeID]
            ),
            "TotalScore", CALCULATE(SUM(FactEmployee_Training[Score]))
        ),
        [TotalScore]
    ),
    REMOVEFILTERS()
)
---------------or-------------
MaxTotalScorePerEmployee =CALCULATE(
MAXX(
    TOPN(
        1,
        ADDCOLUMNS(
            SUMMARIZE(
                ALL(Dim_Employee),
                Dim_Employee[EmployeeID]
            ),
            "TotalScore", CALCULATE(SUM(FactEmployee_Training[Score]))
        ),
        [TotalScore],
        DESC
    ),
    [TotalScore]
),
    REMOVEFILTERS()
)

[kipi_bi]

Thanks a lot, it worked 

Can you let me know why we need to add ADD COLUMNS to get the answer ?

Regards,

Ritz

[Ahmedx]

The SUMMARIZE function uses a clustering method. Read more about it here.
https://www.sqlbi.com/articles/differences-between-groupby-and-summarize/
Also, keep in mind that using the REMOVEFILTERS() function ensures that all filters are removed.

[Rufyda]

Max Score All Employees =
CALCULATE(
MAX(Fact_employee_training[Score]),
REMOVEFILTERS(Dim_Employee)
)

MAX(Fact_employee_training[Score]) finds the highest score in the fact table.

REMOVEFILTERS(Dim_Employee) ignores all filters from the employee dimension, giving the maximum score across all employees regardless of employee-level filters.

If this response was helpful, please accept it as a solution and give kudos to support other community member.

[Kedar_Pande]

@kipi_bi 

Max Score All Employees =
CALCULATE(
MAX(Fact_employee_training[Score]),
ALL(Dim_Employee)
)

This ignores all filters from the employee dimension and returns the maximum score across all employees.

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[kipi_bi]

Thanks but it did not work, max will go to the row level which we would like to avoid

Regards,

Ritz