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



Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
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
    )
)



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

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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

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

    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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
    EduSurveys
    Icon for Advocate II rankAdvocate II
    7 years ago

    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?