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). ...
Need Help
  • AlB's avatar
    AlB
    6 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

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.

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        6 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