SELECTCOLUMNS / FILTER return multiple values

[michaelshparber]

Hello friends,

I have a set of date/time points (PointsInTime table).

For each point I need to show what event was translated on a TV at that point (Events table).

I used a calculated column in PointsInTime table:

EventName = SELECTCOLUMNS(FILTER(Events,Events[EventStartDateTime]<='PointsInTime'[SomeDateTime]&&Events[EventEndDateTime]>='PointsInTime'[SomeDateTime]),"AAA",[Event])

It works fine untill... there are overlapping events (see example photo & attached .pbix).

In this case it shows error:

"A table of multiple values was supplied where a single value was expected."

In case of overlapping events I don't really care which event to show.

So how do I select One of the values returned? I tried Max/Min(Event) but it didn't work.

Please Help

Thanks

Michael

.pbix file:

https://1drv.ms/u/s!AoP_9ampPIT7xRbbex6fx7ZkFl_Z?e=QmNfZs

[Geradav · Accepted solution]

Hi @michaelshparber 

Try the following:

EventWithOverlap =
IF (
    CALCULATE (
        COUNTROWS ( EventsWithOverlap ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ) > 1,
    CALCULATE (
        FIRSTNONBLANK ( EventsWithOverlap[Event], TRUE () ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ),
    BLANK ()
)

Does that give you what you want?

Let us know if that works for you @michaelshparber 

[Geradav · Accepted solution]

Hi @michaelshparber 

Try the following:

EventWithOverlap =
IF (
    CALCULATE (
        COUNTROWS ( EventsWithOverlap ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ) > 1,
    CALCULATE (
        FIRSTNONBLANK ( EventsWithOverlap[Event], TRUE () ),
        FILTER (
            EventsWithOverlap,
            EventsWithOverlap[EventStartDateTime] <= PointsInTime[SomeDateTime]
                && EventsWithOverlap[EventEndDateTime] >= PointsInTime[SomeDateTime]
        )
    ),
    BLANK ()
)

Does that give you what you want?

Let us know if that works for you @michaelshparber 

[Iamnvt · Accepted solution]

@michaelshparber 

you can try this as Calculated column:

EventWithOverlap = CALCULATE(FIRSTNONBLANK(EventsWithOverlap[Event],0),FILTER(EventsWithOverlap,EventsWithOverlap[EventStartDateTime]<='PointsInTime'[SomeDateTime]&&EventsWithOverlap[EventEndDateTime]>='PointsInTime'[SomeDateTime]))

[michaelshparber]

@Iamnvt  @Geradav 

FIRSTNONBLANK()!!!

Works great!

Thank you!

Michael

[Anonymous]

I think you could use this Post. Its does a Time-bound Lookup too.

https://community.powerbi.com/t5/Desktop/DATE-RANGE-LOOKUP/td-p/420514