Forum Discussion

kipi_bi's avatar
kipi_bi
Frequent Visitor
9 months ago
Solved

Problem with MAXX

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.

Need Help
  • Ahmedx's avatar
    Ahmedx
    9 months ago

    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()
)

6 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    9 months ago

    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()
)
  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    9 months ago

    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's avatar
      kipi_bi
      Frequent Visitor
      9 months ago

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

       

      Regards,

      Ritz

  • Rufyda's avatar
    Rufyda
    Super User
    9 months ago

    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.