Help locating error in DAX formula!

afaherty — Thu, 15 Aug 2024 18:14:37 GMT

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:

StudentID	Met or Not	Subject	Subtest
1	Met	Math	Fractions
1	Met	Math	Decimals
1	Met	Math	Equations
1	Met	Math	Measuring
2	Not	Math	Fractions
2	Met	Math	Decimals
2	Met	Math	Equations
3	Met	Math	Fractions
3	Not	Math	Decimals
4	Met	Math	Equations
5	Not	Math	Decimals
5	Not	Math	Equations
5	Met	Math	Measuring

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.

Re: Help locating error in DAX formula!

jgeddes — Thu, 15 Aug 2024 20:07:34 GMT

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

Re: Help locating error in DAX formula!

afaherty — Fri, 16 Aug 2024 14:39:23 GMT

Resolved - I figured it out!

topic Help locating error in DAX formula! in DAX Commands and Tips

Help locating error in DAX formula!

Re: Help locating error in DAX formula!

Re: Help locating error in DAX formula!