sort grid using field from another table

[ushah763]

I need help trying to sort a grid view based on a date field from another table.

I am using the salesforce.com connector to pull data from our instance of the CRM.

I am pulling events and opporuntities from salesforce.com.

So in the Events Table, i have a list of events which occured and their event date.

Event	Event Date
Event 1	9/12/2018
Event 2	10/15/2018
Event 3	11/4/2018
Event 4	1/15/2019
Event 5	2/13/2019
Event 6	1/10/2019
Event 7	7/21/2018
Event 8	6/18/2018
Event 9	3/12/2019
Event 10	2/10/2019
Event 11	1/7/2019
Event 12	12/4/2018

Then on the opportunities table, i have a list of opportunities and the date they were created:

Name	CreatedDate
Acme inc 1	9/16/2011 16:46
Acme inc 2	9/16/2011 16:48
Acme inc 3	5/11/2012 16:30
Acme inc 4	1/29/2013 23:40
Acme inc 5	2/12/2013 2:43
Acme inc 6	2/12/2013 16:06
Acme inc 7	9/27/2013 17:19
Acme inc 8	9/27/2013 17:20
Acme inc 9	11/18/2013 19:42
Acme inc 10	11/25/2013 16:42
Acme inc 11	1/24/2014 17:55
Acme inc 12	2/6/2014 20:08
Acme inc 13	2/20/2015 16:57
Acme inc 14	3/23/2015 19:31
Acme inc 15	3/27/2015 15:57
Acme inc 16	5/18/2015 19:11
Acme inc 17	5/22/2015 2:14
Acme inc 18	5/22/2015 19:05
Acme inc 19	5/22/2015 19:07
Acme inc 20	5/26/2015 14:06

In Power Bi Desktop what i would like to be able to do is in a grid or chart, be able to show all the opportunities with a created date after the EARLIEST event. 

On the events table, the earliest event occure on 6/18/2018. 

So i only want to see opporunties from the second table with a created date > 6/18/2018.

Also, the EARLIEST date may change if i pick a subset of the events, so that date needs to be dymanic.

I can't figure out how to do this.

[v-frfei-msft · Accepted solution]

Hi @ushah763 ,

To create a calculated column in opportunities table.

newcd =
VAR mindate =
    CALCULATE ( MIN ( Events[Event Date] ), ALL ( Events ) )
RETURN
    IF (
        'opportunities'[CreatedDate] >= mindate,
        'opportunities'[CreatedDate],
        BLANK ()
    )

Please find the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @ushah763 ,

A calculated column is not dynamic. So here we should create a measure to work on it.

Measure = var mindate = SELECTEDVALUE(Events[Event Date])
return
IF(MAX('opportunities'[CreatedDate])>=mindate,1,BLANK())

Please find the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @ushah763 ,

To create a calculated column in opportunities table.

newcd =
VAR mindate =
    CALCULATE ( MIN ( Events[Event Date] ), ALL ( Events ) )
RETURN
    IF (
        'opportunities'[CreatedDate] >= mindate,
        'opportunities'[CreatedDate],
        BLANK ()
    )

Please find the pbix as attached.

Regards,

Frank

[ushah763]

Frank,

Thanks for this solution. I had a follow-up question.

How would i leverage the above solution if i had a slicer on the page which picked an event?

So if i pick an event, i want the table of opportunities to only show the ones created post the event date?

Thank you

[v-frfei-msft · Accepted solution]

Hi @ushah763 ,

A calculated column is not dynamic. So here we should create a measure to work on it.

Measure = var mindate = SELECTEDVALUE(Events[Event Date])
return
IF(MAX('opportunities'[CreatedDate])>=mindate,1,BLANK())

Please find the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @ushah763 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank