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

[EduSurveys]

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_ID	NAME	YEAR	LATEST_YEAR	TITLE	DESC	STATUS	OrgIndex
123	Student A	Y1		TRACK TEAM - MEN	Sports	Active	1
123	Student A	Y2		CYCLING CLUB	Sports Clubs	Active	2
123	Student A	Y3		INTERVARSITY CHRISTIAN FELLOWS	Religious	Active	5
123	Student A	Y4		INTERVARSITY CHRISTIAN FELLOWS	Religious	Active	6
465	Student B	Y1		ACTUARIAL CLUB	Clubs	Active	1
465	Student B	Y1		FRISBEE CLUB	Sports Clubs	Active	1
555	Student C	Y2	Y3	THETA XI	Social Fraternity	Pledge	1
555	Student C	Y2	Y3	RP CLUB	Clubs		1
555	Student C	Y2	Y3	IEEE	Technical Society		1
555	Student C	Y3	Y3	THETA XI	Social Fraternity	Active	5
555	Student C	Y3	Y3	RP CLUB	Clubs		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]

Hi @EduSurveys ,

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

Thanks,

Nathaniel

[Nathaniel_C]

Hi @EduSurveys ,

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

23

Student A

Y4

INTERVARSITY CHRISTIAN FELLOWS

Religious

Active

6

[EduSurveys]

@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

[Nathaniel_C]

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

[Nathaniel_C]

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

[EduSurveys]

Correct.

[Nathaniel_C · 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 

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

[EduSurveys]

@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

)

)

[Nathaniel_C]

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

[EduSurveys]

@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.

[Nathaniel_C]

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

[EduSurveys]

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?