topic Re: DAX error support - True/False in DAX Commands and Tips

DAX error support - True/False

karenHO — Mon, 25 Nov 2024 18:01:07 GMT

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

Re: DAX error support - True/False

lbendlin — Mon, 25 Nov 2024 20:02:04 GMT

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.

Re: DAX error support - True/False

Poojara_D12 — Tue, 26 Nov 2024 09:39:38 GMT

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

Re: DAX error support - True/False

Anonymous — Wed, 27 Nov 2024 06:55:57 GMT

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.

Re: DAX error support - True/False

karenHO — Thu, 05 Dec 2024 17:20:52 GMT

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

)

Re: DAX error support - True/False

karenHO — Thu, 05 Dec 2024 17:26:38 GMT

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?