Forum Discussion

emilypinhasi's avatar
emilypinhasi
Frequent Visitor
9 years ago

get value from another table by expression

Hi

i have a table Sprints like:

id  | start date | end date

 

i have another table Issues that has a column that contains date, lets call it changeDate

i would like to add to that table a column that returns the id from the first table if changeDate is between start and end dates

 

tried something like:

FIRSTNONBLANK(Sprints[id],DATESBETWEEN(Issues[changeDate], Sprints[startDate].[Date], Sprints[endDate].[Date]))

but i get errors "A single value for column 'startDate' in table 'Sprints' cannot be determined. "

 

any idea how to acomplish this?

 

5 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    hey,

     

    My both tables look like this

    Table1

    aDate
2017-01-23
2017-01-15
2017-01-26
2017-02-15

    Table2 looks like this

     

    id	startdate	enddate
1	2017-01-01	2017-01-10
2	2017-01-11	2017-01-21
3	2017-01-22	2017-02-10
4	2017-02-11	2017-02-28

    I create a calculated column in Table1 that pulls the id from Table2 from that record where the column aDate is between startdate and enddate (assumption, there is no overlap in the ranges and there are also no gaps in the ranges).

    The calculated column

    CALCULATE(
    MAX('Table2'[id])
    ,FILTER(
        'Table2','Table2'[startdate] <= 'Table1'[aDate] && 'Table2'[enddate] >= 'Table1'[aDate])
)

    Hope this helps

     

    Regards

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      My suggestion would be to expand the Sprints table, so you have a row for each date.

      This is done by importing the dates as whole number and later on these are converted back to dates.

      Now you can merge the other table with the expanded sprints table on the date columns.

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Sprints"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"start date", Int64.Type}, {"end date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {[start date]..[end date]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"start date", "end date"}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Removed Columns", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
    #"Changed Type1"
      let
    Source = Table.NestedJoin(AnotherTable,{"changeDate"},ExpandedSprints,{"Dates"},"ExpandedSprints",JoinKind.LeftOuter),
    #"Expanded ExpandedSprints" = Table.ExpandTableColumn(Source, "ExpandedSprints", {"id"})
in
    #"Expanded ExpandedSprints"
    • emilypinhasi's avatar
      emilypinhasi
      Frequent Visitor
      9 years ago

      thanks, this is whay i needed

       

      but, i do have gaps in the sprint dates, and i assume this is why im getting the same sprint for all rows

       

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        You are refering to the DAX solution?

        The Power Query solution should work fine. :smileywink: