topic Re: DAX measure: Counting where index max value changes for each user in DAX Commands and Tips

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

EduSurveys — Tue, 22 Oct 2019 14:54:07 GMT

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!

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

Nathaniel_C — Tue, 22 Oct 2019 15:02:51 GMT

Hi EduSurveys ,

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

Thanks,

Nathaniel

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

Nathaniel_C — Tue, 22 Oct 2019 15:06:05 GMT

Hi EduSurveys ,

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

Student A

INTERVARSITY CHRISTIAN FELLOWS

Religious

Active

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

EduSurveys — Tue, 22 Oct 2019 17:55:04 GMT

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

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

Nathaniel_C — Tue, 22 Oct 2019 18:13:54 GMT

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

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

Nathaniel_C — Tue, 22 Oct 2019 18:16:20 GMT

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

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

EduSurveys — Tue, 22 Oct 2019 18:56:46 GMT

Correct.

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

Nathaniel_C — Wed, 23 Oct 2019 14:56:10 GMT

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

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

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

EduSurveys — Wed, 23 Oct 2019 20:37:12 GMT

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

)

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

Nathaniel_C — Wed, 23 Oct 2019 21:40:12 GMT

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

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

EduSurveys — Fri, 25 Oct 2019 13:47:54 GMT

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.

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

Nathaniel_C — Fri, 25 Oct 2019 21:46:26 GMT

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

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

EduSurveys — Mon, 28 Oct 2019 12:16:35 GMT

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?