cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EduSurveys
Advocate II
Advocate II

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!
1 ACCEPTED SOLUTION

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 

edu survey 2.PNG

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







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

12 REPLIES 12
Nathaniel_C
Super User
Super User

Hi @EduSurveys ,

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

Thanks,

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

Hi @EduSurveys ,

So, if I understand with 3 students, you can get a total of 5 [DESC]. Get the top [OrgIndex] for each student, then count against each [DESC]. Is that right? If a student had an OrgIndex of 2, and 3.  Only the 3 would be counted anywhere?

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ok so A =6 B=1 c=5 only count those OrgIndex for any club?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Correct.

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 

edu survey 2.PNG

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







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@Nathaniel_C , @ImkeF 

 

Thank you so much! I think this will work for my needs. I had to adjust the filter slightly to include Academic Year (a field that I did not give you but was in my data table and I didn't want it to look for the MAXOrgIndex just for that year), and [DESC], because if a student is in multiple [TITLE] that fall under the same [DESC] (e.g., two distinct religious organizations), I wanted to make sure to count both of them.

 

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

You are welcome.  Learning ALL():smileyvery-happy: the time!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Nathaniel_C ,

 

Okay, so now what if I want to create a histogram of the results from the [Counter]? Is there a way to create a conditional DAX measure that says,

 

DAX measure:

CounterBins =
VAR Bin = IF([Counter]=0,
"No participation",
IF([Counter]<=2,
"1-2 orgs",
IF([Counter]<=5,
"3-5 orgs",
"6 or more orgs")))

RETURN Bin

 

So that I can plot distribution of participation for all students? I can get the above DAX measure in a table, but not in a column plot because I want [CounterBins] to be an axis label and the values to be a distinct count of students who are involved.

Hi @EduSurveys ,

You almost had it! 
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

CounterBins =
IF (
    [Counter] = 0,
    "No participation",
    IF (
        [Counter] <= 2,
        "1-2 orgs",
        IF ( [Counter] > 2 && [Counter] <= 5, "3-5 orgs", "6 or more orgs" )
    )
)

counter bins.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C ,

 

Thanks for your response. My question is not about the DAX itself, but rather that I can't drag the DAX measure into the axis field of a column chart. I can visualize it in a table or matrix, but I can't use it as an axis label in a plot. Is there a way to use the DAX measure as the axis, or am I missing something?

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors