DAX error support - True/False

[karenHO]

I am trying to create a DAX formula that will count the number of students on roll in a school last school year specifically on 2MAY24.

Gemini has offered the below but I'm getting a The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column error.

KH Students on Roll 23/24 =

CALCULATE (
COUNTROWS ( ACADEMIC_CALENDAR_DATES ),
ACADEMIC_CALENDAR_DATES[STUDENT_ON_ROLL] = TRUE,
ACADEMIC_CALENDAR_DATES[STUDENT_ON_ROLL] = "May",
HASONEVALUE ( STUDENT_SCHOOL_ENROLEMENTS[CURRENTLY_ON_ROLL] ),
STUDENT_SCHOOL_ENROLEMENTS[CURRENTLY_ON_ROLL] = TRUE
)

I'm a real beginner to DAX so not quite sure how to get it to count just for May.

Any ideas ??

[Anonymous]

Hi ALL

Firstly  Poojara_D12  and  lbendlin  thank you for your solution!
And @karenHO ,The problem you encountered seems to be because ACADEMIC_CALENDAR_DATES[STUDENT_ON_ROLL] is referenced twice in the dax and is assigned a different value, I tried to improve your DAX as follows, I hope that helps you.

KH Students on Roll 23/24 = 
CALCULATE (
    COUNTROWS ( Student_School_Enrollments_Data ),
    Academic_Calendar_Dates_Data[DATE] = DATE(2024, 5, 2),
    Academic_Calendar_Dates_Data[STUDENT_ON_ROLL] = TRUE,
    Student_School_Enrollments_Data[CURRENTLY_ON_ROLL] = TRUE
)

If you have more recent questions, you can check my pbix file to see if my example data meets your expectations, and I would be honored if my solution solves your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[karenHO]

Hi Thanks for this.

I have used the below formula but I am now getting an error - A function 'PLACEHOLDER' has been used in a True/Fasle expression that is used as a table filter expressions.  This is not allowed.

Any ideas?

KH Students on Roll 23/24 =

CALCULATE (

    COUNTROWS (STUDENT_SCHOOL_ENROLMENTS ),

    ACADEMIC_CALENDAR_DATES[DATE] = DATE(2024, 5, 2),

    ACADEMIC_CALENDAR_DATES[Student_On_Roll] = TRUE,

    STUDENT_SCHOOL_ENROLMENTS[CURRENTLY_ON_ROLL] = TRUE

)

[Poojara_D12]

Hi @karenHO 

Can you please try the below dax:

KH Students on Roll 23/24 =
CALCULATE(
    COUNTROWS(STUDENT_SCHOOL_ENROLEMENTS), -- Counts students currently on roll
    STUDENT_SCHOOL_ENROLEMENTS[CURRENTLY_ON_ROLL] = TRUE, -- Filters students currently on roll
    ACADEMIC_CALENDAR_DATES[DATE] = DATE(2024, 5, 2) -- Filters for the specific date 2-May-2024
)

Please let me know if it works for you.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[karenHO]

This one has sort of worked, however it is only bringing those students who were 'currently on roll' on 2MAY24.

I want to know all students that were 'currently on roll' on 2MAY24, not just those that were first enrolled on 2MAY24.

any ideas?

[lbendlin]

You cannot use HASONEVALUE in that situation.

Instead, create a table variable that applies your filters, and adds the number of "currently on roll") field. Then filter that table variable by the field being equal to 1, and then return the countrows of that.