cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

12 REPLIES 12
Super User

Hi @EduSurveys ,

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

Thanks,

Nathaniel

Proud to be a Super User!

Super User

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

Proud to be a Super User!

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

Super User

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

Proud to be a Super User!

Super User

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

Proud to be a Super User!

Correct.

Super User

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

Proud to be a Super User!

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

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

Proud to be a Super User!

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.

Super User

Hi @EduSurveys ,

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

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors