Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculated Column Based on a DateTime event in a DateTime Range


Hi PowerBI DAX Experts,

 

I have a table with fields: PersonID, Role, ValidFromDateTime and ValidToDateTime.

 

If the person's role is currently valid then the ValidToDateTime is blank (null).

 

Please see sample data below:

 

PersonIDRoleValidFromDateTimeValidToDateTime
1Tester9/1/2019 6:009/10/2019 6:00
1Developer9/10/2019 6:0010/2/2019 8:00
1Manager10/2/2019 8:00 
2Tester8/1/2019 6:009/10/2019 9:00
2Manager9/10/2019 9:00 
3Tester9/1/2019 6:009/10/2019 11:00
3Developer9/10/2019 11:00 


I have another event-related table with fields: PersonID and EventDateTime.  Please see sample data below:

 

PersonIDEventDateTimeRole
19/17/2019 13:00?
29/19/2019 14:00?

 

 

I would like to write DAX code to populate a calculated column named 'Role' based on the EventDateTime.  In the example above, the first Role cell should be calculated to "Developer", and the 2nd cell should be calculated to: "Manager".

 

This is a friendly ask to please suggest a solution for how to calculate the Role calculated column in DAX in this scenario. Also, please show your proposed sample DAX code too if you are willing.

 

Thanks,
Trystan.

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Anonymous 

    Sure. You're right

    Role =
CALCULATE (
    DISTINCT ( Table1[Role] ),
    FILTER (
        Table1,
        Table1[ValidFromDateTime] <= Table2[EventDateTime]
            && ( Table1[ValidToDateTime] > Table2[EventDateTime] || ISBLANK ( Table1[ValidToDateTime] ) )
            && Table1[ID] = Table2[ID]
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

    You should explain the logic behind Role, not just what the result is. Otherwise we have to guess. Try this for your calculated column:

    Role =
CALCULATE (
    DISTINCT ( Table1[Role] ),
    FILTER (
        Table1,
        Table1[ValidFromDateTime] <= Table2[EventDateTime]
            && ( Table1[ValidToDateTime] > Table2[EventDateTime] || ISBLANK ( Table2[EventDateTime] ) )
            && Table1[ID] = Table2[ID]
    )
)

       

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi AIB,

       

      Thanks for your response.  In your response, shouldn't the ISBLANK part be:
      ISBLANK ( Table1[ValidToDateTime] )
      ?

       

      Regarding the logic of role, the role is the person's role when the event occured.

       

      Thanks,
      Trystan.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        AlBPlease see my reply above.