Forum Discussion
DAX Expression Fail
Hi all,
I wonder if you could help me with a bit of DAX. I have a list of students (ECON_1008 Enrols) joined to a list of dates on which they were active online (one to many).
Now I’m only interested in the activity dates (DAY_DAY) that occur between the term start and end dates in the Students table (Enrolment_Term_Begin_Date and Enrolment_Term_End_Date) and am trying to craft an expression which will allow me to identify those.
This is what I tried but it tells me that a table of multiple values was supplied where a single value was expected.
VALID DATE = IF(AND(ALLSELECTED(
'Activity'[DAY_DAY]) >= ALLSELECTED('ECON_1008 Enrols'[Enrolment_Term_Begin_Date]),
ALLSELECTED('Activity'[DAY_DAY]) <= ALLSELECTED('ECON_1008 Enrols'[Enrolment_Term_End_Date])),
"Valid","Invalid")
Can you see where I’m going wrong here?
Thank you!
Hi,
This calculated column formula works
Column = if(CALCULATE(COUNTROWS('Students and courses'),FILTER('Students and courses','Students and courses'[Course_Start]<=EARLIER(Activity[Activity Date])&&'Students and courses'[Course_End]>=EARLIER(Activity[Activity Date])&&'Students and courses'[Student]=EARLIER(Activity[Student])&&'Students and courses'[Course]=EARLIER(Activity[Course])))<>BLANK(),"Valid","Invalid")Hope this helps.
5 Replies
Hi,
Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
This is a simplified version where the first table (students and courses) has a one to many relationship with the second (activity dates). I'm trying to create a DAX expression which will give me the 'Valid Date' column in the second table based on whether it was during the course start end end dates.
students and courses
Student
Course
Course_Start
Course_End
A
MATHS
1/01/2020
31/01/2020
B
ENG
1/02/2020
29/02/2020
C
HIST
1/03/2020
31/03/2020
activity datesStudent
Course
Activity Date
Valid Date
A
MATHS
1/01/2020
Valid
A
MATHS
10/01/2020
Valid
A
MATHS
15/01/2020
Valid
A
MATHS
1/02/2020
Invalid
A
MATHS
6/04/2020
Invalid
B
ENG
9/01/2020
Invalid
B
ENG
5/02/2020
Valid
B
ENG
10/02/2020
Valid
B
ENG
18/02/2020
Valid
B
ENG
29/02/2020
Valid
C
HIST
8/02/2020
Invalid
C
HIST
11/03/2020
Valid
C
HIST
12/03/2020
Valid
C
HIST
20/03/2020
Valid
C
HIST
1/04/2020
Invalid
Hi,
This calculated column formula works
Column = if(CALCULATE(COUNTROWS('Students and courses'),FILTER('Students and courses','Students and courses'[Course_Start]<=EARLIER(Activity[Activity Date])&&'Students and courses'[Course_End]>=EARLIER(Activity[Activity Date])&&'Students and courses'[Student]=EARLIER(Activity[Student])&&'Students and courses'[Course]=EARLIER(Activity[Course])))<>BLANK(),"Valid","Invalid")Hope this helps.
