Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
afaherty
Helper V
Helper V

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:

 

afaherty_0-1723739241279.png

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

 

afaherty_1-1723739323420.png

 

 

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 2
afaherty
Helper V
Helper V

Resolved - I figured it out!

 

jgeddes
Super User
Super User

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

jgeddes_0-1723752413588.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.