Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ushah763
Helper I
Helper I

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.

 

EventEvent Date
Event 19/12/2018
Event 210/15/2018
Event 311/4/2018
Event 41/15/2019
Event 52/13/2019
Event 61/10/2019
Event 77/21/2018
Event 86/18/2018
Event 93/12/2019
Event 102/10/2019
Event 111/7/2019
Event 1212/4/2018

 

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

 

NameCreatedDate
Acme inc 19/16/2011 16:46
Acme inc 29/16/2011 16:48
Acme inc 35/11/2012 16:30
Acme inc 41/29/2013 23:40
Acme inc 52/12/2013 2:43
Acme inc 62/12/2013 16:06
Acme inc 79/27/2013 17:19
Acme inc 89/27/2013 17:20
Acme inc 911/18/2013 19:42
Acme inc 1011/25/2013 16:42
Acme inc 111/24/2014 17:55
Acme inc 122/6/2014 20:08
Acme inc 132/20/2015 16:57
Acme inc 143/23/2015 19:31
Acme inc 153/27/2015 15:57
Acme inc 165/18/2015 19:11
Acme inc 175/22/2015 2:14
Acme inc 185/22/2015 19:05
Acme inc 195/22/2015 19:07
Acme inc 205/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.

 

3 ACCEPTED SOLUTIONS
v-frfei-msft
Community Support
Community Support

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 ()
    )

Capture.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

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())

2.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

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 ()
    )

Capture.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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())

2.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.