Forum Discussion

ushah763's avatar
ushah763
Helper I
7 years ago
Solved

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 opporu...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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's avatar
    v-frfei-msft
    7 years ago

    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's avatar
    v-frfei-msft
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
ushah763
Helper I
7 years ago

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's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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's avatar
      v-frfei-msft
      Community Support
      7 years ago

      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