cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nadja273
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Nadja273 

I build a sample to have a test.

Receive Table:

2.png

Action Table:

3.png

Send Table:

4.png

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:

1.png

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. 

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

Hi @Nadja273 

I build a sample to have a test.

Receive Table:

2.png

Action Table:

3.png

Send Table:

4.png

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:

1.png

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. 

View solution in original post

Hi @RicoZhou , 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors