Calculate value between two dates if date falls between two dates in another table

[Anonymous]

What I am trying to accomplish create a calculated column in my date table to return the value of a column from another table where I only have a start and end date. It should be pulling in the value of 'Event Header_Basic'[POS Event Code], if DimDate[Date] is between 'Event Header_Basic'[Date Start] and 'Event Header_Basic'[Date End].

What I've tried is:

Column = CALCULATE(
            VALUES('Event Header_Basic'[POS Event Code]),
            FILTER(
                    'Event Header_Basic',
                    'Event Header_Basic'[Date Start]<=(DimDate[Date])
                      && 'Event Header_Basic'[Date End]>=(DimDate[Date])
            )
)

I recieved an error "A table of multiple values was supplied where a single value was expected.". 

Does anyone have any other suggestions?

Thanks

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use DAX below.

Column =
CONCATENATEX (
    FILTER (
        'Event Header_Basic',
        'Event Header_Basic'[Date Start] <= ( DimDate[Date] )
            && 'Event Header_Basic'[Date End] >= ( DimDate[Date] )
    ),
    'Event Header_Basic'[POS Event Code],
    ","
)

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use DAX below.

Column =
CONCATENATEX (
    FILTER (
        'Event Header_Basic',
        'Event Header_Basic'[Date Start] <= ( DimDate[Date] )
            && 'Event Header_Basic'[Date End] >= ( DimDate[Date] )
    ),
    'Event Header_Basic'[POS Event Code],
    ","
)

[FerKant03]

I have a similar case and this DAX "almost" works for me, the only challenge that I have is that I have multiple lines with multiple results, hence I end up getting something like "A,A,A,B,B,B,C,C". How can this formula be modified if I have 1 or 2 more criteria (columns) to match?

thanks in advance @v-chuncz-msft 

Rgds,

Fer

[Anonymous · Accepted solution]

You're the best, thanks!