Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
You are welcome.
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
102 | |
93 | |
73 | |
60 | |
59 |