Forum Discussion

afaherty's avatar
afaherty
Icon for Helper V rankHelper V
2 years ago

Help locating error in DAX formula!

Greetings,

I have some sort of error in my DAX formula and I can't figure out what is wrong. I was hoping someone could lend their eyes & expertise. I have student test data (what you see here is fake data). Of course there are other subjects aside from Math in the real data but for simplicity, I only present Math here:

 

StudentIDMet or NotSubjectSubtest
1MetMathFractions
1MetMathDecimals
1MetMathEquations
1MetMathMeasuring
2NotMathFractions
2MetMathDecimals
2MetMathEquations
3MetMathFractions
3NotMathDecimals
4MetMathEquations
5NotMathDecimals
5NotMathEquations
5MetMathMeasuring

 

We do not care about the subtests. I realize not every student has data for each subtest. 

 

I have a slicer showing a 50% met limit, 60% met limit, 70% met limit, 80% met limit:

 

These met limit percentages are all listed in their own table titled "Met Limits":

 

 

 

I attempted to figure out how many & the percentage of students who "Met" 50% of their math tests, how many met 60%, how many met 70%, and how many met 80%. I need to be able to see that with a click of the slicer.

 

Here is what I have:

 

Measure 1 = 

VAR MetLimit = SELECTEDVALUE('Met Limits'[Met Limit])

VAR CurrentStudentTable = CALCULATETABLE('Tests',ALLEXCEPT('Tests','Tests'[StudentID],'Tests'[Subject]))

VAR Mets = FILTER(CurrentStudentTable,'Tests'[Met or Not]="Met")

VAR Result = IF(DIVIDE(COUNTROWS(Mets),COUNTROWS(CurrentStudentTable))>=MetLimit, "Met", "Not")

VAR CurrentResult = SELECTEDVALUE('Tests'[Met or Not])

RETURN IF(ISINSCOPE('Tests'[Subject]), CurrentResult, Result)

 

So what is the problem? The problem is that it is labeling some students who ALWAYS met as "not." It is also mis-categorizing those who only have 2 math tests where one is "met" and one is "not." It should be labeling those as "not" but it is labeling them incorrectly as "met." I can't understand why!  Thank you for anyone who can help.

 

2 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    Something like this may work for you...

    Measure = 
var _selectedLimit =
SELECTEDVALUE('Met Limit'[Met Limit])
var _vTable = 
SUMMARIZE(
    'Table',
    'Table'[Subject],
    'Table'[StudentID],
    "__metPerc", DIVIDE(COUNTROWS(FILTER('Table', 'Table'[Met or Not] = "Met")),COUNTROWS('Table'),0)
)
var _result =
COUNTROWS(
    FILTER(_vTable, [__metPerc] >= _selectedLimit)
)
Return
_result

    The _vTable variable creates a virtual table that summarizes your intial table by subject and studentID, calculating the "met" percentage for each line. It would look something like this with your data (and another subject I added)...

    The _result variable is just counting rows that met or exceed the met limit that is selected.
    Hope this helps...