Forum Discussion

DHB's avatar
DHB
Helper V
2 years ago
Solved

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

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

  • DHB's avatar
    DHB
    Helper V
    2 years ago

    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's avatar
      Ashish_Mathur
      Super User
      2 years ago

      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's avatar
        DHB
        Helper V
        2 years ago

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