Forum Discussion

yogeshk77's avatar
yogeshk77
Helper I
3 years ago
Solved

Fetch Relative Date from another table

Need help to fetch relevant date field from other table please.

 

I have below two tables

 

Release Calendar Table - Showing Date & Release number for some of the dates

 

 

 

Pull Request Table - Showing Code PR ID, Status & Committed Date

 

 

 

Now, I need a DAX experession for a new Calculated Column in Pull Request Table, to show Release Date
And Logic is -
( PR Commit Date > Release Date that belongs to Release number containing either SM RL OR RM RL words AND Merge Status = Succeeded )
AND
( OR Commit Date <= Release Date that belongs to RElease Number containing either SM RL OR RM RL words AND Merge Status = Succeeded )

 

For e.g. End Result should look something like this in Pull Request Table

 

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    CalculatedColumn=IF(PullRequestTable[Merge Status]="succeeded",MINXX(FILTER(ReleaseCalendarTable,(CONTAINSSTRING(ReleaseCalendarTable[Release Number],"SM RL")||CONTAINSSTRING(ReleaseCalendarTable[Release Number],"RM RL"))&&ReleaseCalendarTable[Release Date]>=PullRequestTable[Commit Date]),ReleaseCalendarTable[Release Date]))

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi yogeshk77 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create measures.

    Measure = var _1=IF(CONTAINSSTRING(MAX('Release Calendar Table'[Release Number]),"SM RL"),1,IF(CONTAINSSTRING(MAX('Release Calendar Table'[Release Number]),"RM RL"),1,BLANK()))
RETURN _1
    Measure 2 = MINX(FILTER(ALL('Release Calendar Table'),'Release Calendar Table'[Release date]>=MAX('Pull Request Table'[Commit Date])&&[Measure]=1),'Release Calendar Table'[Release date])

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • yogeshk77's avatar
      yogeshk77
      Helper I
      3 years ago

      Thank you for help, but I needed a Custom Column instead of a measure. Got the solution now. Thank you anyways.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    CalculatedColumn=IF(PullRequestTable[Merge Status]="succeeded",MINXX(FILTER(ReleaseCalendarTable,(CONTAINSSTRING(ReleaseCalendarTable[Release Number],"SM RL")||CONTAINSSTRING(ReleaseCalendarTable[Release Number],"RM RL"))&&ReleaseCalendarTable[Release Date]>=PullRequestTable[Commit Date]),ReleaseCalendarTable[Release Date]))