Forum Discussion
sort grid using field from another table
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.
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
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
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
4 Replies
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
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
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
