Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

@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

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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
Not applicable

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
Not applicable

@AlBPlease see my reply above.

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.