Forum Discussion

EduSurveys's avatar
EduSurveys
Icon for Advocate II rankAdvocate II
7 years ago
Solved

DAX measure: Counting where index max value changes for each user

Hi, I am looking for a DAX measure that calculates current membership in a co-curricular organization based on max OrgIndex for each student. The problem is that I have one row for every org that the student was involved in by their year in college, so I may have multiple rows per student, and their max OrgIndex will be different depending on their involvement.

 

STUDENT_IDNAMEYEARLATEST_YEARTITLEDESCSTATUSOrgIndex
123Student AY1 TRACK TEAM - MENSportsActive1
123Student AY2 CYCLING CLUBSports ClubsActive2
123Student AY3 INTERVARSITY CHRISTIAN FELLOWSReligiousActive5
123Student AY4 INTERVARSITY CHRISTIAN FELLOWSReligiousActive6
465Student BY1 ACTUARIAL CLUBClubsActive1
465Student BY1 FRISBEE CLUBSports ClubsActive1
555Student CY2Y3THETA XISocial FraternityPledge1
555Student CY2Y3RP CLUBClubs 1
555Student CY2Y3IEEETechnical Society 1
555Student CY3Y3THETA XISocial FraternityActive5
555Student CY3Y3RP CLUBClubs 5

 

 

DAX measure that I tried, but it only does the max for the column, not for each student:

CountOrg = CALCULATE(

COUNT(
FactOrgMembership[ORGN_TITLE])
, FILTER(all(FactOrgMembership[OrgIndex])
, FactOrgMembership[OrgIndex]=MAX(FactOrgMembership[OrgIndex])
)
)

 

Calculated column I used to generate the OrgIndex.

OrgIndex =
RANKX(
FILTER(
'FactOrgMembership',
EARLIER(FactOrgMembership[STUDENT_ID]) = FactOrgMembership[STUDENT_ID]),
FactOrgMembership[ORGN_ASGN_YEAR],
,
ASC
)
 
Thanks for your help!
  • Nathaniel_C's avatar
    Nathaniel_C
    7 years ago

    Hi EduSurveys ,

    Here we go. Courtesy of ImkeF  She is amazing!

    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    PBIX Here 

    f

    Counter = 
COUNTROWS (
    FILTER (
        SELECTCOLUMNS ( Surv, "ID", Surv[STUDENT_ID], "Org_Index", Surv[OrgIndex] ),
        VAR MAXOrgIndex =
            CALCULATE ( MAX ( Surv[OrgIndex] ), ALLEXCEPT ( Surv, Surv[STUDENT_ID] ) )
        RETURN
            [Org_Index] = MAXOrgIndex
    )
)



12 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi EduSurveys ,

    Will you give us an example of your expected end product? You can dummy it up in Excel.

    Thanks,

    Nathaniel

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hi EduSurveys ,

      So for Student A is this what you would expect to see "based on max OrgIndex for each student."

      23Student AY4 INTERVARSITY CHRISTIAN FELLOWSReligiousActive6
      • EduSurveys's avatar
        EduSurveys
        Icon for Advocate II rankAdvocate II
        7 years ago

        Nathaniel_C 

         

        So for CountOrg, I want it to count the # of students involved right now in orgs by DESC:

        1 involved in Religious (Student A, OrgIndex=6), 2 in Clubs (Student B, OrgIndex=1; Student C, OrgIndex=5), 1 in Sports Clubs (Student B, OrgIndex=1), and 1 in Social Fraternity (Student C, OrgIndex=5). I only want the DAX measure to count membership where OrgIndex=Max for that student. Does that make sense?

         

        Religious = 1

        Clubs = 2

        Sports Clubs =1

        Social Fraternity = 1