Forum Discussion
DAX Expression Fail
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.
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 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 |
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.
Thank you Ashish_Mathur that works perfectly. I really appreciate your time.
You are welcome.