DAX Expression Fail

[DHB]

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!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[DHB]

Hi @Ashish_Mathur 

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 dates

Student	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

[Ashish_Mathur · Accepted solution]

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.

[DHB]

Thank you @Ashish_Mathur that works perfectly.  I really appreciate your time.

[Ashish_Mathur]

You are welcome.