Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |