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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
puzzle
Frequent Visitor

DAX Measure to capture if someone had an open request at time of contact

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 IDPerson IDEvent TypeEvent Start DateEvent End Date
11Request01/04/2020 
21Contact21/06/202221/06/2022

 

Thanks in advance 🙂

9 REPLIES 9
some_bih
Super User
Super User

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!





Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

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)





Did I answer your question? Mark my post as a solution!

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @puzzle simple IF in calculated column  could help you with it (if this the shown is data). try it





Did I answer your question? Mark my post as a solution!

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors