Forum Discussion

Marcin's avatar
Marcin
Icon for Helper V rankHelper V
8 years ago
Solved

DAX calculation max by group

Hi,   I am looking for healp with modeling in PowerBI model for case shown below.    In model I have two dimension tables Users and Domains , one fact table Evaluation for Users by Skills.    I...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Marcin,

     

    Add below calculated columns in 'Evaluation' table.

    Average =
CALCULATE (
    AVERAGE ( Evaluation[Score] ),
    ALLEXCEPT ( Evaluation, Users[UserID], Evaluation[Skill] )
)

Max average =
CALCULATE (
    SELECTEDVALUE ( Domian[SkillVCategory] ),
    FILTER (
        ALLEXCEPT ( Evaluation, Evaluation[Userid] ),
        Evaluation[Average]
            = CALCULATE (
                MAX ( Evaluation[Average] ),
                ALLEXCEPT ( Evaluation, Evaluation[Userid] )
            )
    )
)

    Then, create column in 'Users' table with this DAX formula.

    Hightest score skill =
LOOKUPVALUE ( Evaluation[Max average], Evaluation[Userid], Users[UserID] )
     
    Best regards,
    Yuliana Gu
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 

Also, if you could post your example data in a form that can be copied and pasted that would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

  • Marcin's avatar
    Marcin
    Icon for Helper V rankHelper V
    8 years ago

    Thank you for links, unfortunately I stiill don't know how to get that SkillCategory that I am looking for ( Category not Score Value)

     

    I will be gratefull for help with this.