Custom Column matching values in different tables followed by nested if/else function

[Anonymous]

Hi everyone,

I am currently building a power BI report to track the number of received, currently worked on, and sent emails in several inboxes to understand current demand and changes over time. One measure I am aiming to produce is the number of emails that were received, but not yet actioned and/or responded to, providing direct information on the workload admission staff is currently confronted with.

My data is summarised in three tables, one for received emails, one for emails that were moved into subfolders (i.e. in the actioning stage), and one for sent emails. These tables are linked via a conversation ID, so that I have information on which row in the sent folder relates to which received email. This in an example of what these tables look like:

To	From	Subject	Importance	Date	Time	Conversation ID
jane.doe@example.com	john.doe1@example.com	abc	normal	19/06/2021	08:30:11	1
jane.doe@example.com	john.doe2@example.com	def	normal	20/06/2021	09:13:22	2
jane.doe@example.com	john.doe3@example.com	xyz	normal	20/06/2021	15:55:04	3

In order to get the overall number of emails that still need actioning/responding at a given time, I think I first need to insert a custom column and come up with a way to assign a categoric outcome (i.e. has been responded to/ has been actioned/has only been received). I can then present these categories in my report, possibly by setting up a couple of measures around the data in this column.

I am trying to come up with the formula for this custom column, but am struggling with its complexity and was hoping someone could help. This is what I want to do:

• Within each conversation ID that is assigned to each email in the Emails_received table, it needs to first check whether there is an entry in the emails_actioned and/or emails_sent table.
  • If there is no match to a received email ID in either table, this row needs to be assigned the category of ‘needs responding’.
  • If there is only a match in ID in the emails_actioned, it needs to be categorised as ‘in progress’
• If there is a match in conversation ID in the sent folder, it now needs to compare the dates of when the most recent email was received and sent, as sometimes there are multiple matches for each ID.
  • If the date sent > date received the correct label should be ‘completed’, otherwise ‘needs responding’.

I think it needs to start with something like the LookupValue function, followed by some nested IF/ELSE functions, but I don’ really know where to start to make this work.

Any help would be appreciated!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I build a sample to have a test.

Receive Table:

Action Table:

Send Table:

Please make sure your the underlying data of your Time column is datetime type, or power bi will show time in 1899 and you may get wrong result.

Here is my measure:

Measure = 
VAR _ActionID = VALUES(Actions[ID])
VAR _SendID = VALUES(Send[ID])
VAR _RecentReceiveDate = MAXX(FILTER(ALL('Received emails'),'Received emails'[ID]=MAX('Received emails'[ID])),'Received emails'[Date])
VAR _RecentRecevieTime = MAXX(FILTER(ALL('Received emails'),'Received emails'[ID]=MAX('Received emails'[ID])),'Received emails'[Time])
VAR _RecentActionDate = MAXX(FILTER(ALL(Actions),Actions[ID] = MAX('Received emails'[ID])),Actions[Date])
VAR _RecentActionTime = MAXX(FILTER(ALL(Actions),Actions[ID] = MAX('Received emails'[ID])),Actions[Time])
VAR _RecentSendDate = MAXX(FILTER(ALL(Send),Send[ID] = MAX('Received emails'[ID])),Send[Date])
VAR _RecentSendTime = MAXX(FILTER(ALL(Send),Send[ID] = MAX('Received emails'[ID])),Send[Time])
RETURN
IF(MAX('Received emails'[ID] )in _SendID&& _RecentSendDate>=_RecentReceiveDate&& _RecentSendTime>_RecentRecevieTime,"completed",
IF(MAX('Received emails'[ID]) in _ActionID && _RecentActionDate>=_RecentReceiveDate&& _RecentActionTime>_RecentRecevieTime,"In Progress","needs responding"))

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @v-rzhou-msft , 

Thank you for your response and sorry for the delay in responding. Your solution looks great, however, in the meantime, I ended up having to restructure my dataset, and now have all information in one table (Admission_All), with an extra column called 'stage' that contains information on which of the 3 previous tables the data was in (i.e. Received, Moved and Sent). I also ended up keeping Date and Time as one variable, so that should enable me to convert your 6 steps in the middle into just 3. I am trying to replicate your measure above, but so far I only managed to adapt the code for the variables in the middle. This is what I did so far:

VAR Action ID 

VAR SendID 

VAR _RecentReceiveDate = MAXX(FILTER(ALL('Admission_All'),('Admission_ALL'[Conversation ID]=MAX('Admission_All'[Conversation ID])) && Admission_All[Stage] = “Received”),'Admission_All'[Date Time])

VAR _RecentActionDate = MAXX(FILTER(ALL('Admission_All'),('Admission_ALL'[Conversation ID]=MAX('Admission_All'[Conversation ID])) && Admission_All[Stage] = “Moved into Subfolder”),'Admission_All'[Date Time])

VAR _RecentSentDate = MAXX(FILTER(ALL('Admission_All'),('Admission_ALL'[Conversation ID]=MAX('Admission_All'[Conversation ID])) && Admission_All[Stage] = “Sent”),'Admission_All'[Date Time])

RETURN
If....

I am unsure how to create the first 2 variables to tell Power BI to just look at the data filtered for Actioned or Sent Conversation ID, which then doesn't allow me to write the last section around the IF function. (Sorry if this is a dumb question, but I have to admit I have never set up variables like this before and am a bit lost!)

I was also wondering if this information needs to be added as a column instead of as a measure as I was hoping to do additional analyses with this data, such as highlighting the date of the oldest email that still needs responding, as that would help us understand how we are doing in terms of timely responses to our customers. 

Any advice would be appreciated!

Best wishes,

Nadja