how to identify linked activity

[Anonymous]

Hi All,

I've two tables namely opportunities and events. I would like to know if there is any linked acitivity in future for an opportunity from the opportunity table. Both tables are linked on ID field.

Regards,

Viresh

[v-xuding-msft]

Hi @Anonymous ,

Please share some sample data and expected results that will make us understand about your requirement clearly.

Please see this post regarding How to Get Your Question Answered Quickly: 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Xue Ding

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

[Anonymous]

Hi, Please find the attached screenshot.

first table contains an ID with future close date and which is available in second table with future activitydate.

Get list of all open opportunities (Isclosed = false)

For each opportunity check if there is at least one linked activity with a date in the future.

If true, YES else NO

Assume 4 of 80 open opportunities do not have one or more activity in the future

Calculated % is 76/4+76) = 95%

Regards,

Viresh

[v-xuding-msft]

Hi @Anonymous ,

I created a sample and added some data in it based on your sample data. You could try and check if it is what you want.

Measure = 
var max_record = CALCULATE(MAX(Events[ActivityDate]),ALLEXCEPT(Events,Events[WhatID]))
var opportunaty_date = CALCULATE(MAX(Opportunaties[ClosedDate]),FILTER(ALLEXCEPT(Opportunaties,Opportunaties[ID]),Opportunaties[IsClosed] = "Flase"))
return
IF(max_record <= opportunaty_date , "Yes","No")

Measure 2 = 
var yes = CALCULATE(DISTINCTCOUNT(Opportunaties[ID]),FILTER(Opportunaties,[Measure] = "Yes"))
var all_id = DISTINCTCOUNT(Opportunaties[ID])
return
DIVIDE(yes,all_id)

Best Regards,

Xue Ding

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

[PattemManohar]

@Anonymous Please try using RELATED or LOOKUPVALUE functions..

Any sample data and expected output will be helpful to provide you an accurate solution.