Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
PersonID | Role | ValidFromDateTime | ValidToDateTime |
1 | Tester | 9/1/2019 6:00 | 9/10/2019 6:00 |
1 | Developer | 9/10/2019 6:00 | 10/2/2019 8:00 |
1 | Manager | 10/2/2019 8:00 | |
2 | Tester | 8/1/2019 6:00 | 9/10/2019 9:00 |
2 | Manager | 9/10/2019 9:00 | |
3 | Tester | 9/1/2019 6:00 | 9/10/2019 11:00 |
3 | Developer | 9/10/2019 11:00 |
I have another event-related table with fields: PersonID and EventDateTime. Please see sample data below:
PersonID | EventDateTime | Role |
1 | 9/17/2019 13:00 | ? |
2 | 9/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.
Solved! Go to 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
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
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
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
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |