Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there, hope you're well!
I am looking for some help please? Is it possible to create a measure in DAX which looks at whether someone had an open request at the time a contact they have had with us? When a request is open, it will have no end date.
The data would look something like this:
Event ID | Person ID | Event Type | Event Start Date | Event End Date |
1 | 1 | Request | 01/04/2020 | |
2 | 1 | Contact | 21/06/2022 | 21/06/2022 |
Thanks in advance 🙂
Hi @puzzle your calculated column Event End Date could be
Event End Date =IF (<YourTableName>[Event Type]="Request", BLANK(), Event Start Date)
Did I answer your question? Mark my post as a solution! Kudos Appreciated!
Proud to be a Super User!
Hey @some_bih
I think I need to be a bit clearer in my explanation (sorry)
I am looking for a DAX measure which will take the end date of the request and compare it against the start date of the contact. If either the end date of the request is after the start date of the contact or the request end date is blank (meaning it has not ended), then this will mean a person has had a contact with us whilst having an open request.
I would then need to make a flag for both scenarios, Y being if they have had a contact during an open request and N if not.
Hope that makes things a bit clearer, sorry and thanks in advance 🙂
Hi @puzzle two conditions seems possible. Still, if your data is updated / generated as shown on picture, like if you simple change status from Request to Contact (and Start date is already inserted before) then formula for column I sent you should do the rest.
Is your processing of data different?
Proud to be a Super User!
Hey @some_bih ,
These are seperate interactions, there isn't any change from event type. A request will always stay a request, and a contact is always a contact. Someone could have one request from 2020 but have 20 contacts from 2021 whilst that request is still open.
What I am trying to achieve is to use the contact date(s) against the range of dates between the start and end date of the request to determine if they made this contact whilst their request was open.
Hope that makes sense?
Hi @puzzle I still do not understand how your data / table is organized. I understand your data is as shown on first post, and only Event End date should be provided.
If you have data organized in different way, like two or more columns please share (and relationship)
Proud to be a Super User!
Hey @some_bih
Sorry I'm not being very clear! The table above is how the data is organised, so there aren't seperate columns for these dates - they all appear under one column and each event type fills the same columns.
Does that help? Or are you looking for something more specific?
@puzzle If data is organized that way, then column i sent you should work? I do not understand why not or there is something else you want as output.
Proud to be a Super User!
Hi @puzzle simple IF in calculated column could help you with it (if this the shown is data). try it
Proud to be a Super User!
Hey @some_bih , how so?
I need it to calculate on a row by row basis as to whether or not there was a service open at the time of the contact. Because of the way that the data is structured, the events all share the same start date column.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
14 | |
14 | |
9 |